On Azure account, I have created an AAD user.
I created Azure SQL database and assigned the newly created AAD user as a Server administrator.
The server has both AAD account login and SQL Server login.
I tested connecting to server through SSMS using AAD user, it works. I tested adding other AAD users to database, it works (in SSMS).
Now I'm testing the connection to database from Console app.
I want to connect to database using AAD user credentials.
Console app is supposed to work without user logging in, so I'm want to hardcode user into connection string.
My connection string is:
string connectionString = @"Server=tcp:test-server.database.windows.net,1433; Authentication=Active Directory Password; Database=mydb; User Id=aad_user_name@myaccount.onmicrosoft.com; Password=mypassword";
Console breaks as soon as it tries to create SqlConnection, from what I can see in parsing connection string.
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 MyApp.Program.TestDb() in C:\path\Program.cs:line 28
Project file:
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net6.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.Data.SqlClient" Version="4.1.0" />
<PackageReference Include="System.Data.SqlClient" Version="4.8.3" />
</ItemGroup>
</Project>
So, I'm using latest .NET 6, Microsoft.Data.SqlClient and System.Data.SqlClient.
I got it. I was not supposed to use System.Data.SqlClient lib at all. There is new implementation of SqlConnection in Microsoft.Data.SqlClient lib and it worked.
Problem was that VS 2022 was suggesting to use System.Data.SqlClient.