Closed ltrzesniewski closed 4 years ago
Hi @ltrzesniewski
I do see this documentation: https://docs.microsoft.com/en-us/previous-versions/system-center/configuration-manager-2007/bb632649(v=technet.10)?redirectedfrom=MSDN#required-communication-protocols
But I'm not sure if that would mean there's no support for Named Pipes for Kerberos in driver.
@saurabh500 Are you aware of any such limitation for Named Pipes where Kerberos is not supported? Or should we attempt to fix this?
Hi @cheenamalhotra,
I'm trying to connect with TCP here, not with named pipes.
The intriguing part is that the following connection strings work just fine on Linux as well: Data Source=SQL_TEST,1433;Integrated Security=true Data Source=tcp:SQL_TEST;Integrated Security=true
This is TCP, default connection mechanism is Named Pipes for driver.
Also - If you disable Named Pipes & Shared Memory on your target server, then driver will default to TCP.
Oh OK, I thought that the driver first tried to connect with TCP and then tried named pipes if TCP failed.
I guess I'd still expect the driver to try TCP if named pipes fail or to only try TCP if Kerberos is not supposed to work with named pipes.
I checked with the server admin, and it turns out that named pipes are actually disabled on this server.
I also made a quick check with sqlcmd -S np:SQL_TEST -E
on Windows, and I get the following error:
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
Which confirms that named pipes are disabled.
I made sure that sqlcmd -S tcp:SQL_TEST -E
connects fine. Actually, sqlcmd -S SQL_TEST -E
(with out the tcp:
part) also works fine both from the Windows and Linux machines.
There's also Shared Memory protocol which is chosen default if you're connecting to SQL Server on the same machine. You can try to disable that to ensure you connect only with TCP.
But as you said sqlcmd
works and SqlClient does not, that does not look right then. We will investigate and get back to you.
Thanks!
That SQL Server is on a different machine, so I did not ask the server admin if the shared memory protocol is enabled or not.
We do rely on the port number to create the SPN to authenticate to failing which we create a default SPN.
I quickly browsed the code at https://github.com/dotnet/SqlClient/blob/master/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNIProxy.cs#L344
With Kerberos Auth (which is the only kind of integrated auth supported on Linux in the driver) we construct the SPN of the server using the connectivity information in the following format.
MSSQLSVC/hostname_with_reverse_dnsLookup:port
If no port is specified then we try MSSQLSVC/hostname_with_reverse_dnsLookup
For SQL running on default port, we expect that the SQL Server admin has registered the SPN MSSQLSVC/hostname_with_reverse_dnsLookup in the AD along with MSSqlSvc/hostname_with_reverse_dnsLookup:1433 Typically for default port we expect the following two SPN formats.
MSSQLSVC/hostname_with_reverse_dnsLookup MSSQLSVC/hostname_with_reverse_dnsLookup:port
On Windows this is not a problem because the Kerberos auth fails and the driver falls back to NTLM and there is no SPN in the picture there. Hence it seems more seamless, but for Kerberos we do depend on the SPN for SQL to be registered correctly.
Hi @saurabh500,
The server was initially registered with SPNs which all included the port number - the admin told me this was the best practice.
Then we tried adding SPNs without the port, but the SqlClient didn't seem to connect better with this change. We only tested that quickly though, so we may have missed something.
I'll show your message to the server admin on Monday, and I guess we'll double-check if the SPN without the port number was registered properly, and also if we are really inadvertently using NTLM on Windows.
Hello @ltrzesniewski
I'll show your message to the server admin on Monday, and I guess we'll double-check if the SPN without the port number was registered properly, and also if we are really inadvertently using NTLM on Windows.
Sounds good and it will be interesting to know more about this experiment.
Then we tried adding SPNs without the port, but the SqlClient didn't seem to connect better with this change. We only tested that quickly though, so we may have missed something.
I have observed that it takes sometimes for the new SPN registration to propagate, but I don't know what causes it. How long did you wait to try integrated authentication after you registered the SPN without the port number?
I do agree that an SPN without a port number may not be desirable.
@cheenamalhotra It might be interesting to see the behavior of sqlcmd on Linux. What it's behavior with Integrated Authentication is, when it comes to default port specification (i.e without 1433 and with it) Since sqlcmd uses the ODBC driver, this would let us know what the behavior of the ODBC driver is.
How long did you wait to try integrated authentication after you registered the SPN without the port number?
Yes, we were aware there's a propagation time, and we waited a few minutes after the change, but maybe that wasn't enough. We'll double check that.
Here are the additional test results.
Adding SPNs without the port number:
The other day, we have added the following SPNs without the port number:
MSSQLSvc/SQL_TEST
MSSQLSvc/SQL_TEST.<fqdn>
That didn't work. But the SQL_TEST
name is actually a CNAME, not the real machine name returned by reverse DNS.
So today, we added the SPNs with MachineName
(the real host name the reverse DNS lookup returns), to get the following in addition to the SPNs with the port number:
MSSQLSvc/SQL_TEST
MSSQLSvc/SQL_TEST.<fqdn>
MSSQLSvc/MachineName
MSSQLSvc/MachineName.<fqdn>
That SPN config allows SqlClient to connect fine from Linux when using the Data Source=SQL_TEST;Integrated Security=true
connection string.
I can see the MSSQLSvc/MachineName.<fqdn>@<REALM>
entry in the klist
output (without a port number).
NTLM on Windows:
We ran the following query on our servers, where Windows clients are connected using connection strings with Data Source=CNAME;Integrated Security=true
:
SELECT auth_scheme FROM sys.dm_exec_connections
None of the connected clients used NTLM (except for a single shared-memory connection). All SPNs configured on these servers include the port number.
Summary
To sum up, if we have a SQL Server with port numbers in all of its SPNs, and a connection string in the form Data Source=CNAME;Integrated Security=true
:
sqlcmd
connects fineWhat is your recommendation as to the SPN configuration? Are we supposed to add SPNs without the port number, or should SqlClient be able to connect with this configuration?
Hi @saurabh500
It might be interesting to see the behavior of sqlcmd on Linux. What it's behavior with Integrated Authentication is, when it comes to default port specification (i.e without 1433 and with it) Since sqlcmd uses the ODBC driver, this would let us know what the behavior of the ODBC driver is.
I checked with ODBC team internally, and they default to 1433 if no port is specified.
If no port is specified, we should probably try:
MSSQLSVC/hostname_with_reverse_dnsLookup:1433
@ltrzesniewski This is probably a sqlclient bug, since sqlcmd is able to connect.
@ltrzesniewski
Could you test again with this driver: Microsoft.Data.SqlClient.2.1.0-dev-i627.zip (Included fix to default to 1433 port)
<PackageReference Include="Microsoft.Data.SqlClient" Version="2.1.0-dev-i627" />
Thanks @cheenamalhotra !
I can confirm that this version works fine on Linux, and connects to a server with the port number in its SPN, when using a connection string like Data Source=CNAME;Integrated Security=true
.
Will this change be backported to System.Data.SqlClient as well?
Hi @ltrzesniewski
Thanks for confirming. It depends on the impact of the issue and the bar is very high for backporting fixing to S.D.S since it's now in LTS servicing mode. Are you/your organization dependent on this issue and is it a blocker?
No worries, it's not a blocker since adding tcp:
to the data source in the connection string works around the issue.
Thanks!
@cheenamalhotra i agree with bug evaluation.
Describe the bug
When trying to use SqlClient on Linux with integrated security, I noticed that the following connection string:
Data Source=SQL_TEST;Integrated Security=true
Would result in the following error when trying to connect:
This connection string works fine on Windows.
The intriguing part is that the following connection strings work just fine on Linux as well:
Data Source=SQL_TEST,1433;Integrated Security=true
Data Source=tcp:SQL_TEST;Integrated Security=true
The machine is correctly configured and attached to the Active Directory with Kerberos. The user has a valid TGT.
After a quick look at the code, I guess the following condition may need to be updated:
https://github.com/dotnet/SqlClient/blob/ead806e87a15be9f54c99db2ebe8918025c20fc9/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNIProxy.cs#L336
Here's the full stack trace:
To reproduce
This can be reproduced with a simple test program run on Linux (just replace
SQL_TEST
with a valid SQL Server host name):Expected behavior
I expect the
Data Source=SQL_TEST;Integrated Security=true
connection string to work exactly the same way asData Source=SQL_TEST,1433;Integrated Security=true
on Linux.Further technical details
Microsoft.Data.SqlClient version: 2.0.0 .NET target: .NET Core 3.1 SQL Server version: SQL Server 2019 Operating system: Ubuntu 18.04 LTS