2

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.

enter image description here

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.

Mahesh Waghmare
  • 726
  • 9
  • 27
Regianni
  • 229
  • 1
  • 3
  • 11
  • Try removing tcp: from the connection string, I don't believe you need to specify the port (1433) either. – TrevorBrooks Nov 15 '19 at 16:32
  • See this article: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure – TrevorBrooks Nov 15 '19 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 Nov 15 '19 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 Jun 16 '20 at 08:54

1 Answers1

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

AndrewGentry
  • 161
  • 1
  • 15