Differences Connecting to SQL Server with Domain Users in C# and Go
Recently I've had the opportunity to explore C# and Blazor since coming from a mostly Go-background. Something I work to do in most applications is connect to SQL Server.
In Go, regardless if it's a SQL User or Domain user, I can use a connection string like so and it will just work™️
dsn := `server=alan.bearblog.dev\instance;user id=username;password=shhthisisasecret;database=Blog`
If the user is on a domain, all I have to do is add Domain\username instead of username and again, it will just work™️
When starting in C# and Blazor, the same string would yield this error on attempted connection:
Error: Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user 'DOMAIN\username'.
what
After lots of searching I eventually found out that the SQL Drivers were working differently. In Go, the driver I was using tried to make all of the domain-related stuff automatic based on what was present in the string. If DOMAIN\username was there, then act as if you are that user and log in. If there's no domain, but there is a username, then act as if it is a SQL login. If there's no user information, but we are running on Windows, attempt single-sign on. These features stemming purely from what was available in the connection string made life easy.
C# was not as clear about what was happening. Much of this I believe is in the name of security and tight integration with Windows. The main feature I was longing after, because it worked cross-platform and didn't require a domain-joined computer, was called Impersonation. Searches show a variety of questions regarding Windows Impersonation across versions of .NET and .NET Core, with each future version changing how this has to be done and requiring a more explicit definition, rather than falling back.
In some versions of .NET Core (4?), supposedly this is all that had to be done to trigger the Impersonation:
"iisSettings": {
"windowsAuthentication": true,
"anonymousAuthentication": false,
...
}
At some point .NET Core or the SQL Driver stopped acting in this way, and the solution was to call Windows features via P/Invoke. Ultimately, I'm not sure how much that even works anymore as of .NET Core 7, but my setup is not typical as I'm working from a non-domain joined Windows 11 ARM build in Parallels on macOS so I can't for sure point to a breaking change, although the Go version still works in that same virtual machine.
The best supported ways to connect to SQL Server in C# using System.Data.SqlClient seem to be limited to:
- SQL credentials to log directly into SQL Server in the connection string
- Set Integrated Security = True, on a domain-joined Windows host
- Set Integrated Security = True, on a Kerberos ticketed host
Through this, I've been able to run .NET Core 7 directly on my Mac and log into SQL with domain credentials and Kerberos. Now I can start developing!