PSU 3.0 - SQL Support

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?

1 Like

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.

1 Like

Hey :slight_smile:

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 :slight_smile:

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.

1 Like