azureazure-sql-databaseazure-active-directoryazure-sql-server

Azure SQL Database - Active Directory integrated authentication

I would like to achieve following:

Avoid SQL authentication on Azure for my production configuration and use Active Directory integrated authentication

When I go to the connection string section of Azure and copy following connection string:

Server=[my server name];Initial Catalog=[my db name];Persist Security Info=False;User ID=[my user name];MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Integrated";

and try using it I get following exception:

Exception message: Cannot use 'Authentication=Active Directory Integrated' with 'User ID', 'UID', 'Password' or 'PWD' connection string keywords., Exception stacktrace: 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)

As I have very limited db admin/infrastructure experience I am unaware why the 'User ID' breaks everything when it is explicitly listed in the connection string I get on Azure.

A few things to note:

Solution

  • Your connection string should look like below on C#:

    string ConnectionString =
@"Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Integrated; Initial Catalog=testdb;";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();

    Please read this documentation and this documentation for more information.