CDS T-SQL endpoint pt 3 – SqlClient

As a developer, the first thing I want to do with any new tool is try and integrate or extend it with my own code. This is always one of the real benefits of CDS and the Power Platform for me, as it’s so much more open to extensibility than others I could mention. Adding a T-SQL endpoint should just make this much wider.

The demos I’ve seen so far for this have all been around using SQL Server Management Studio. That’s great for ad-hoc queries, but what about if I want to do some regular data export/integration? If I was talking to any other SQL Server database I’d probably drop to C# code and connect to it through there, so I’ve given that a go here.

Connecting with SqlClient

You can use either System.Data.SqlClient or the newer Microsoft.Data.SqlClient to connect, either work just fine. The connection string you’ll need is in the format:

server=contoso.crm.dynamics.com,5558;user id=your-username;password=your-password;Authentication="Active Directory Password"

Note the Authentication parameter at the end to use the correct authentication method rather than the standard SQL Server authentication.

This is a quick code snippet to export the names of your account records:

using (var con = new SqlConnection("server=contoso.crm.dynamics.com,5558;user id=your-username;password=your-password;Authentication=\"Active Directory Password\""))
{
  con.Open();

  using (var cmd = con.CreateCommand())
  {
    cmd.CommandText = "SELECT name FROM account";

    using (var reader = cmd.ExecuteReader())
    {
      while (reader.Read())
      {
        Console.WriteLine(reader.GetString(0));
      }
    }
  }
}

Note that you don’t need to specify a database name in the connection string – you’ll only see one database when you connect and you’re automatically connected to that database so there’s no need to change. There’s no harm in including it though, so if you use

server=contoso.crm.dynamics.com,5558;user id=your-username;password=your-password;Authentication="Active Directory Password";database=contoso

it will still work just fine.

Broken Connection Strings

I did try a few other variants of connection strings but I only found one that didn’t seem to work so well:

server=tcp:contoso.crm.dynamics.com,5558;user id=your-username;password=your-password;Authentication="Active Directory Password"

Using the tcp: prefix on the server name is quite a common pattern in samples, but in this case you’ll get the error:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

Application User

Having the username & password in the connection string is A Bad Thing. In most integration scenarios you should probably be using an application user instead. Azure SQL Database and now CDS supports this using the AccessToken property on the connection using code like:

var clientId = "<guid>";
var clientSecret = "<secret string>";
var aadTenantId = "<guid>";

var aadInstance = "https://login.windows.net/{0}";
var resourceId = "https://contoso.crm.dynamics.com/";

var authenticationContext = new AuthenticationContext(string.Format(aadInstance, aadTenantId));
var clientCredential = new ClientCredential(clientId, clientSecret);

var authenticationResult = await authenticationContext.AcquireTokenAsync(resourceId, clientCredential);

using (var con = new SqlConnection("server=contoso.crm.dynamics.com,5558"))
{
  con.AccessToken = authenticationResult.AccessToken;
  con.Open();

  using (var cmd = con.CreateCommand())
  {
    cmd.CommandText = "SELECT name FROM account";

    using (var reader = cmd.ExecuteReader())
    {
      while (reader.Read())
      {
        Console.WriteLine(reader.GetString(0));
      }
    }
  }
}

A few things to note here, especially if you’re copying code from an Azure SQL Database sample.

First, the AuthenticationContext class gets the access token asynchronously. Please don’t just use var authenticationResult = authenticationContext.AcquireTokenAsync(ResourceId, clientCredential).Result; as you’ll almost certainly end up with deadlocks at some point.

Second, all the Azure SQL Database sample code will use https://database.windows.net/ as the resource ID that’s passed to AcquireTokenAsync. This gives you a token that’s valid for that resource. If you use that to try to access CDS you’ll get the error:

The HTTP request is unauthorized with client authentication scheme 'Anonymous'. The authentication header received from the server was 'Bearer authorization_uri=https://login.microsoftonline.com/<guid>/oauth2/authorize, resource_id=https://contoso.crm.dynamics.com/

This is because you’re not actually accessing Azure SQL Database, but CDS pretending to be a SQL database. You therefore need to change the resource ID to be the URL of your CDS instance (https://contoso.crm.dynamics.com/ in this sample) and it should all work smoothly.

It does take some time to get the access token, so if you’re calling this code regularly make sure you cache the tokens (taking care to watch out for token expiry – the tokens are only valid for 1 hour so you’ll need to periodically get a new one).

Once you’ve got connected you can use the connection in the same way as any other SqlConnection – because that’s exactly what it is!

Other Posts

This is part of a series of posts on the T-SQL endpoint, read more about it:

  1. Connecting
  2. First Thoughts
  3. SqlClient
  4. Performance
  5. EntityFramework / ORM
  6. Aggregates
  7. Extensibility
  8. Security

Join the conversation

14 Comments

  1. Hi Mark
    Thank you for your investigations around t-sql endpoint!
    If I got you right, there should be no problem in using this endpoint in a cds plugin for example?

  2. great stuff! Would you know any way of using a AADirectory app to get the accesstoken and use in on cds? I.e. not using a application user, but someone authenticating though a webapp.

  3. When attempting to follow your example “Connecting with SqlClient”, I see an error message that states (in summary) “Consent between the first party application “insert-code-number-here” and first party resource “insert-code-number-here” must be configured via preauthorization. Visit https://identitydocs.azurewebsites.net/static/aad/preauthorization.html for details

    I’m not familiar with this at all and could only guess it’s related to something I need to do in Azure. Did you not see this issue because your setup is different/on premise?

    1. It’s not something I’ve seen before. There’s nothing special about my deployment, it’s a standard Online environment (the T-SQL endpoint is only available online).

      Are you trying to authenticate as a regular user or an application user?

      1. I didn’t discover what the error message meant but I was able to connect as an application user instead.

        I’ve not found a way to use variables with the end point which would be useful for ad-hoc queries via ssms. They don’t seem to be supported at all.

        Many thanks for a very informative & accurate blog which I’ll continue to follow – and for sql4ds.

        For anyone else reading this, here is a good overview.
        https://xrmtoolcast.libsyn.com/all-things-sql-for-cds-with-mark-carrington

    1. Theoretically yes, though the authentication is likely to be an issue. I haven’t been able to get it to work, but it may be complicated by the fact that all my accounts require MFA which isn’t an option in the linked server configuration. Let me know if you get it to work though!

  4. Not so sure on the CDS pretending to be SQL part:
    The Select sql statements do not go through the plugin execution such as Retrieve(Multiple): so it feels a bit more low level than the above statement?
    Some other “weirdness”:
    1)dynamicpropertyinstance has not been exposed
    2)when selecting dynamicpropertyassociation or dynamicpropertyoptionsetitem you get the latter returned(correct result when calling with fetchxml)

    1. 3) dynamicproperty: regardingobjectid shows up in the table definition but is not returned in a select nor can you use it in the whereclause.

  5. Hello Mark,

    Thank you very much for this article. It saved me. My only question is why does Microsoft does not recommend this on production?
    Will this work on production environment?

    Nevertheless, highly appreciate this and other articles of yours.
    Regards,
    Madhu

    1. Yes, it does work on a production environment. It’s still in preview so it could change at any time, and we’ve also seen it withdrawn temporarily because they discovered a security vulnerability, so it’s not recommended for production use because nothing about it is guaranteed until it’s officially released as generally available (GA)

Leave a comment

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.