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:

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

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:

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!

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.