Azure SQL Database - Active Directory integrated authentication

4
votes

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:

  • SQL authentication works
  • I have Active Directory admin set
2
azureazure-sql-databaseazure-active-directoryazure-sql-server

2 Answers

4
votes

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.

-5
votes

Try changing the IP address in your Azure database. There is a tutorial on Lynda.com on this.