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=contosoit 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:
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?
It should be possible, but not recommended. When you’re in a plugin you’ve already got an authenticated IOrganizationService that’s possibly part of a transaction – the recommendation is to stay within that pipeline wherever possible.
https://twitter.com/rjmax/status/1259149378792460288?s=20
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.
This page goes through a similar process for getting a user token to call Microsoft Graph – the process would be very similar except for the scope, which would be user_impersonation.
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?
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?
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
With this new feature Can we go ahead and create a linked server in sql for Dynamics?
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!
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)
3) dynamicproperty: regardingobjectid shows up in the table definition but is not returned in a select nor can you use it in the whereclause.
I did another post more recently on my latest thinking on exactly what this endpoint is doing:
http://markcarrington.dev/2020/08/04/msdyn365-internals-t-sql-endpoint/
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
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)
Hi Mark,
thank you for all the work you are spending in this project.
I am already using SQL 4 CDS via the CRM toolbox, and this is helpfull not only to update data easily, but also to get a better understanding of CRM structures. Currently I am trying to find a way to integrate CRM data in simple office documents, like writing marketing letters to a couple of contacts. Is my understanding correct, that the system.data.sqlclient class you are updating is the same as the ADO class used in VB or VBA? I would have to install the SSMS update, but then I could also use the SQL client in VBA projects, is that correct?
No, unfortunately this is based on ADO.NET which is very different from plain ADO. It may be possible to expose these classes as COM objects which would make them available from VB but this isn’t something I’m able to test/support.
Hi Mark,
sorry to bother you again, we do not manage to get a response using a Sql4CdsConnection. The connection can be created using ClientSecret as Authentication method like this
AuthType=ClientSecret;url=https://*****.api.crm4.dynamics.com;ClientId=61a26ce8-8fd3-47a8-9edf-eee6a693832a;ClientSecret=*************
but when we create a reader command using the example code you give in github, then the commend times out when performing the cmd.ExecuteReader()
We did not install the SSMS package previously – will that be needed? Or do you have another hint what could go wrong?
Thank you in advance,
Matthias
Huge thanks for working out the Anonymous auth scheme error!
Here’s an MSAL Version of your ADAL, fill in the id’s as required.
string dataverseUrl = “https://mycompany.crm3.dynamics.com”
string resourceId = dataverseUrl + “/”;
string scopes = [$”{resourceId}/.default”]; //Yes it’s, double //
var authority = $”https://login.microsoftonline.com/{aadTenantId}”;
var app = ConfidentialClientApplicationBuilder.Create(clientId)
.WithClientSecret(clientSecret)
.WithAuthority(new Uri(authority))
.Build();
var result = await app.AcquireTokenForClient(scopes).ExecuteAsync();