M
Will do - happy to be a guinea pig :-)
I've found one small hiccup already I think - our Azure Active Directory instance has enforced MFA on accounts, so I won't be able to use Windows Authentication for ProGet to connect to SQL Server and I've been advised to use a Service Principal instead, but System.Data.SqlClient doesn't support authenticating to SQL Server with a service principal:
# build the connection string
PS C:\temp> $connectionString = ([ordered] @{
"Server" = "my-sql-server.database.windows.net"
"Authentication" = "Active Directory Service Principal"
"Encrypt" = "True"
"Database" = "my-sql-database"
"User Id" = "00000000-0000-0000-00000000000"
"Password" = "<secret-generated-by-azure>"
}).GetEnumerator() | foreach-object { $_.Name + "=" + $_.Value } | join-string -Separator ";"
# show the connection string
PS C:\temp> $connectionString
Server=my-azure-database.database.windows.net;Authentication=Active Directory Service Principal;Encrypt=True;Database=my-proget-database;User Id=00000000-0000-0000-0000-000000000000;Password=<secret-generated-by-azure>
PS C:\temp>.\inedosql.exe update . --connection-string=$connectionString
Unhandled Exception: System.ArgumentException: Invalid value for key 'authentication'.
at System.Data.Common.DbConnectionStringBuilderUtil.ConvertToAuthenticationType(String keyword, Object value)
at System.Data.SqlClient.SqlConnectionString.ConvertValueToAuthenticationType()
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, SqlCredential credential)
at Inedo.DbUpdater.SqlServer.SqlServerDatabaseConnection.GetConnection()
at Inedo.DbUpdater.SqlServer.SqlServerDatabaseConnection.ExecuteTable[TResult](String query, Func`2 adapter, SqlTransactiontransaction, SqlParameter[] args)
at Inedo.DbUpdater.SqlServer.SqlServerDatabaseConnection.GetChangeScriptVersion(SqlTransaction transaction)
at Inedo.DbUpdater.SqlServer.SqlServerDatabaseConnection.GetState()
at Inedo.DbUpdater.Program.Update(String scriptPath, String connectionString, Boolean force)
at Inedo.DbUpdater.Program.Run(ArgList args)
at Inedo.DbUpdater.Program.Main(String[] args)
See System.ArgumentException: Invalid value for key 'authentication' above.
The answer is apparently to migrate to the Microsoft.Data.SqlClient package which supports additional Azure-based authentication methods - that's obviously quite a big change though but I figured I'd mention it so you were aware...
See https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver16#using-service-principal-authentication for more details.
Here's the equivalent login from SQL Server Management Studio, which works with the credentials I've put in my connection string above:
In the meantime I'll follow up with our secops team to see if I can get an exemption from the MFA policy for a new Azure AD account...