Product: PowerShell Universal
Version: 3
Hi,
When looking over the documentation for PSU 3 and the SQL Server support, it seems that the settings are defined in appsettings.json.
Persistence - PowerShell Universal
From my limited experience with docker, i cant seem to find this file, when i created the containers using this guide - Docker - PowerShell Universal
So have i missed something, or is this feature “only” available at the moment, for full blown Windows Installations?
With docker, you can use environment variables rather than the appsettings.json file itself.
For example, in your dockerfile.
ENV Data__ConnectionString=Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI;
ENV Plugins:0=SQL
Hey,
Where did you find that info?
I cant seem to find it in the documentation.
Ive looked at SQL Server connection strings - ConnectionStrings.com to build the connection string, but its not using the same configurations as you specified.
Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;
ENV Data__ConnectionString=Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI;
ENV Plugins:0=SQL
I was missing from the docs so I just came up with that info myself.
I updated the docs to include this info here since it wasn’t clear:
Your SQL connection string looks fine. I was just using the integrate auth version and I just copy and pasted that from the same site.
With your new settings, are you having any success connecting to SQL?
Hey Adam,
I followed your steps, and managed to connect to the DB instance using this dockerfile:
FROM ironmansoftware/universal:latest
LABEL description="Universal - The ultimate platform for building web-based IT Tools"
EXPOSE 5000
VOLUME ["/home/data"]
ENV Data__RepositoryPath /home/data/Repository
ENV Data__ConnectionString Server=192.168.2.40;Database=PSUTest;User Id=psutest;Password=SuperSecretPW;
ENV Plugins:0=SQL
ENV UniversalDashboard__AssetsFolder /home/data/UniversalDashboard
ENV Logging__Path /home/data/logs/log.txt
ENTRYPOINT ["./Universal/Universal.Server"]
ENV TZ Europe/Copenhagen
RUN apt-get install -y tzdata
After a bit of hassle with the containers, they started up just fine, and the database was updated with tables.
But it seems that the jobs / endpoints, are not synchronized between the “workers” - or have i misunderstood something here?
To synchronize the configuration data (scripts, endpoints, etc), you will need to use a git sync configuration. The job runners should be automatically configured (you can check by navigating to the /hangfire page and looking at the connected machines). You should also be able to run a script on one machine and see the output on the other.
We’re gonna try upgrading to 3 next week, and this seems like the right thread to ask these questions in.
Does PSU support Managed Identity access to the database if I’m using Azure SQL? i.e. with Authentication=Active Directory Default in the connection string?
What level of access to the database does PSU require? Just go the nuclear option and give it db_owner?
Thanks!
That’s a good question about the managed identity. I will have to look into that. We’ve primarily been testing with username\password auth or integrated auth locally.
Opened an issue here to look into that: Documentation: Azure Managed Identity for SQL Server Connection · Issue #1244 · ironmansoftware/issues · GitHub
PSU will require privileges to create and update the database tables. You will likely need db_owner. The reason is that the PSU service actually runs the EF migrations at startup. We have considered providing SQL scripts for the migration but will wait to hear from customers if that’s something that is desired. In that scenario, the migration would happen manually through some deployment process and PSU would just be using the database and not modifying it.
Hey 
I was unaware that we needed to have an Git configured for this, and was also hoping that the database would store the scripts, endpoints, etc.
Do you have an updated documentation, on what envirionment variables to use for Git / Docker container combined?
But it seems that the job status arent synchronized properly:
Even that the hangfire page shows, that the agents are working:
Oh, now that is weird. With SQL support on the only place it should be getting job data from is the database so I’m not quite sure why that would be empty.
Can you send me a log to adam@ironmansoftware.com?
Done
Hey @adam,
So we finally got around to switching to Azure SQL for the backing database today, and bad news about the managed identity “Azure Directory Default” connection string.
I created a SQL database user for our managed identity and made sure it was an owner of our database:
CREATE USER [wiot-psu2] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [wiot-psu2];
… then I set the connection string and “plugins” environment variables:
Data__ConnectionString
Server=tcp:<server name>.database.windows.net,1433;Initial Catalog=psu;Authentication=Active Directory Default
Plugins:0
SQL
… but when the app service restarted, it failed to start. The PSU log had this error:
Unhandled exception. System.ArgumentException: Invalid value for key 'authentication'.
at Microsoft.Data.Common.DbConnectionStringBuilderUtil.ConvertToAuthenticationType(String keyword, Object value)
at Microsoft.Data.SqlClient.SqlConnectionString.ConvertValueToAuthenticationType()
at Microsoft.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
at Microsoft.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
at Microsoft.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
at Microsoft.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
at Microsoft.Data.SqlClient.SqlConnection..ctor(String connectionString)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.CreateDbConnection()
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.get_DbConnection()
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass18_0.<Exists>b__0(DateTime giveUp)
at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_0`2.<Execute>b__0(DbContext c, TState s)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation, Func`2 verifySucceeded)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists(Boolean retryOnNotExists)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists()
at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
at PowerShellUniversal.SQL.DatabaseFeature.InitializeDatabase(IConfiguration configuration) in C:\actions-runner\_work\universal\universal\src\PowerShellUniversal.SQL\Plugin.cs:line 53
at Universal.Server.Services.PluginService..ctor(IConfiguration configuration) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Services\PluginService.cs:line 73
at Universal.Server.Startup.ConfigureServices(IServiceCollection services) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Startup.cs:line 63
at System.RuntimeMethodHandle.InvokeMethod(Object target, Span`1& arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.InvokeCore(Object instance, IServiceCollection services)
at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.<>c__DisplayClass9_0.<Invoke>g__Startup|0(IServiceCollection serviceCollection)
at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.Invoke(Object instance, IServiceCollection services)
at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.<>c__DisplayClass8_0.<Build>b__0(IServiceCollection services)
at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.UseStartup(Type startupType, HostBuilderContext context, IServiceCollection services, Object instance)
at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.<>c__DisplayClass13_0.<UseStartup>b__0(HostBuilderContext context, IServiceCollection services)
at Microsoft.Extensions.Hosting.HostBuilder.CreateServiceProvider()
at Microsoft.Extensions.Hosting.HostBuilder.Build()
at Universal.Server.Program.<>c__DisplayClass3_0.<Main>b__0(Options o) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Program.cs:line 71
at CommandLine.ParserResultExtensions.WithParsed[T](ParserResult`1 result, Action`1 action)
at Universal.Server.Program.Main(String[] args) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Program.cs:line 53
The “invalid value for authenticate” error tells me that the version of your Entity Framework SQL provider doesn’t know about Active Directory Default. For reference, the app that I have this working in is using version 4.1.0 of Microsoft.Data.SqlClient.
Once we switched over to standard “User ID=…;Password=…” authentication in the connection string (after creating a dedicated SQL user) things started working.
Really hoping we can get the managed identity authentication working, because it’s awesome not having to store secrets like passwords in connection strings!
Cheers,
Matt
I’ve bumped the version of Microsoft.Data.SqlClient to 4.1.0. This will be in PSU 3.2.
Hi, did this get resolved? I am using PSU 3.4.4 and i cannot get an integrated connection string working for Azure SQL but i can get username/password working which is not ideal as mentioned above… If it is working, would it be possible to get an example of a working connection string (with personal items removed)
Thanks,
Neil.
I must admit I never ended up trying it again. After a few false starts I was too keen to just get into PSU so I left it as username/password. I vaguely recall that even once 3.2 was released I tried once without success. I guess I didn’t want to pester Adam too much over it.
I have been attempting too. I have been able to connect use PSU with SQL support using SQL authentication in Azure.
When I attempted to use either of the passwordless connection strings below, I encountered the same errors as seen by @mabster:
Active Directory Managed Identity Connection String:
Server=<SQL Server>; Authentication=Active Directory Managed Identity; Encrypt=True; Database=<Database Name>; User Id=<Azure Managed Identity ClientID>;"
Active Directory Default Connection String:
Server=<SQL Server>; Authentication=Active Directory Default; Encrypt=True; Database=<Database Name>;
Error:
[14:32:50 INF] Start installing Hangfire SQL objects...
Unhandled exception. System.ArgumentException: Keyword not supported: 'authentication'.
at System.Data.Common.DbConnectionOptions.ParseInternal(Dictionary`2 parsetable, String connectionString, Boolean buildChain, Dictionary`2 synonyms, Boolean firstKey)
at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Dictionary`2 synonyms)
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
at Hangfire.SqlServer.SqlServerStorage.<.ctor>b__6_0()
at Hangfire.SqlServer.SqlServerStorage.CreateAndOpenConnection()
at Hangfire.SqlServer.SqlServerStorage.UseConnection[T](DbConnection dedicatedConnection, Func`2 func)
at Hangfire.SqlServer.SqlServerStorage.Initialize()
at Hangfire.SqlServer.SqlServerStorage..ctor(String nameOrConnectionString, SqlServerStorageOptions options)
at Hangfire.SqlServerStorageExtensions.UseSqlServerStorage(IGlobalConfiguration configuration, String nameOrConnectionString, SqlServerStorageOptions options)
at PowerShellUniversal.SQL.SqlFeature.ConfigureHangfire(IServiceCollection serviceProvider, IConfiguration configuration) in C:\actions-runner\_work\universal\universal\src\PowerShellUniversal.SQL\Plugin.cs:line 40
at Universal.Server.Services.PluginService.ConfigureHangfire(IServiceCollection serviceCollection, IConfiguration configuration) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Services\PluginService.cs:line 85
at Universal.Server.UniversalAutomationStartup.ConfigureServices(IServiceCollection services, IPluginService pluginService)
at Universal.Server.Startup.ConfigureServices(IServiceCollection services) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Startup.cs:line 80
at System.RuntimeMethodHandle.InvokeMethod(Object target, Span`1& arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.InvokeCore(Object instance, IServiceCollection services)
at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.<>c__DisplayClass9_0.<Invoke>g__Startup|0(IServiceCollection serviceCollection)
at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.Invoke(Object instance, IServiceCollection services)
at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.<>c__DisplayClass8_0.<Build>b__0(IServiceCollection services)
at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.UseStartup(Type startupType, HostBuilderContext context, IServiceCollection services, Object instance)
at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.<>c__DisplayClass13_0.<UseStartup>b__0(HostBuilderContext context, IServiceCollection services)
at Microsoft.Extensions.Hosting.HostBuilder.CreateServiceProvider()
at Microsoft.Extensions.Hosting.HostBuilder.Build()
at Universal.Server.Program.<>c__DisplayClass3_0.<Main>b__0(Options o) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Program.cs:line 76
at CommandLine.ParserResultExtensions.WithParsed[T](ParserResult`1 result, Action`1 action)
at Universal.Server.Program.Main(String[] args) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Program.cs:line 58
I reviewed an SQL authentication connection and the output given showed this:
[14:46:30 INF] Start installing Hangfire SQL objects...
[14:46:31 INF] Hangfire SQL objects installed.
Checking my SQL Server, I can see that I am finally getting connections to my SQL Database.
Turning my attention to Hangfire leads me to this forum post - Using Microsoft.Data.SqlClient in Hangfire.SqlServer? - question - Hangfire Discussion
It appears Hangfire 1.7.25 does not have a version of Microsoft.Data.SqlClient greater than version 3. It appears when version 1.8 comes out this may be achievable by default.
It looks like there has been some success to workaround this in the Hangfire community - Using Hangfire with Azure and Managed Identity - question - Hangfire Discussion. As I am using the docker container, I expect I will not be able to implement this change. I would be curious to see if this worked if a 1.8 release candidate was built into PSU, however this would not be suitable for a production release. I am wondering if it is feasible to modify the startup.cs file in the released docker images without breaking PSU for anyone who has it working.
I suspect we will have to wait until Hangfire release v1.8 and this is built into PSU for passwordless Azure Active Directory support.
I would be curious to know if anyone else has been able to find a workaround for this for anyone using Azure and the Docker container release. I hope my notes save other engineers some investigation time pulling AD apart to identify why this does not work.
As of 3.7.0 the Hangfire fault has been fixed and i am now able to connect using passwordless authentication.
There were a few manual steps I had to take to set this up in azure using an Azure Container Instance, Managed User ID and SQL Database:
Following database creation, run the following query on the database.
CREATE USER [manageduser] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [manageduser];
Finally, I had to swap out my connection string in my environment variables
Server=tcp:servername.database.windows.net,1433;Initial Catalog=databasename; Authentication=Active Directory Default;
Following these changes, Hangfire loaded up.
[11:29:56 INF] Start installing Hangfire SQL objects...
[11:29:56 INF] Hangfire SQL objects installed.
Special thanks to @adam for making the needed changes to Hangfire.