Trouble connecting to Sql Azure Db from C# using Active Directory password

Question

I am setting up an azure database to which the CRM 360 Data Export Service (DES) needs to connect. I can do this manually using SSMS and logging on using my azure account.

And then setting up the DES service account as an external provider.

CREATE USER DynamicsDataExportService FROM EXTERNAL PROVIDER

All good, however I wish to automate the process, so I need to logon to the service with in C# and connect and run the code that way.

I am running core 2.2 with System.Data.SqlClient 4.7 using the following code:

        var cs = "data source=tcp:dvzxfsdg-sql.database.windows.net,1433;initial catalog=dfsdfs-sqldb; Authentication=Active Directory Integrated";
        using (var connection = new SqlConnection(cs))
        {
            connection.Open();

Annoyingly It gives the error

"System.ArgumentException: 'Keyword not supported: 'authentication'.'".

I am pretty sure the mistake I am making is fairly basic, but I cannot seem to the get to the bottom of it. So any pointers are gratefully received.

Try removing tcp: from the connection string, I don't believe you need to specify the port (1433) either. — TrevorBrooks
– TrevorBrooks, Commented Nov 15, 2019 at 16:32
See this article: learn.microsoft.com/en-us/azure/sql-database/… — TrevorBrooks
– TrevorBrooks, Commented Nov 15, 2019 at 16:38
What I've done with SQL is to acquire the Azure AD access token for SQL DB first, and then assign it to the SqlConnection before opening it. The connection string only defines the server and database name, nothing else. — juunas
– juunas, Commented Nov 15, 2019 at 18:40
I finally realised that dotnet core 2.0 did not support this type of logon, so eventually had to shell out to a framework version of the app. The same code worked perfectly then. Thank you to all for trying to help. Much appreciated. — Regianni
– Regianni, Commented Jun 16, 2020 at 8:54

AndrewGentry · Accepted Answer · 2019-11-15 16:54:21Z

0

You might want to try using the SQLConnectionStringBuilder to properly format your connection string. This helps avoid typos and issues you might be running into with a plaintext connection string. After that, you can actually set the AuthenticationMethod directly on the builder.

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder ()
    {
        DataSource = "ServerName",
        InitialCatalog = "DatabaseName",
        etc...
    }

builder.Authentication = SqlAuthenticationMethod.ActiveDirectoryInteractive;
SqlConnection sqlConnection = new SqlConnection(builder.ConnectionString);

If the SQL db is an Azure SQL db, then you can view the overview page of the resource and find the "Connection Strings" blade as well. This might help troubleshoot the issue you're having with the connection string you're providing.

Check out this answer about building connection strings for more info

edited Nov 15, 2019 at 16:54

answered Nov 15, 2019 at 16:42

AndrewGentry

1711 silver badge16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trouble connecting to Sql Azure Db from C# using Active Directory password

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related