--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
Ok, here it is:
ackets 94-95-96 are the SYN/SYN-ACK/ACK of the TCP handshake (I'm going to stop pointing those out from here forward).
You are looking at packet #97. Wireshark interprets this as "TDS", or Tabular Data Stream. It has options and stuff, some of which are mildly interesting to me but not to this analysis.
The next packet, packet #98, you can see the details of below. It is also interpreted by Wireshark as "TDS". This is the first packet from a SQL client to the SQL Server where he is saying hello, asking to log in, asking if he can do an encrypted connection, etc. That is what you would see if I opened up all the option fields.
The reply comes back in packet 97:
Hmmm... another TDS with options. Maybe there will be something cool in the next packet?
No, another TDS with more options.... Maybe packet 99 will be better?
Well now we have a little something different - we see this is a Pre-Login message of type TLS Exchange which is by the top red arrow, but that is all we know. Or, more accurately, that is all the protocol dissector inside Wireshark is telling us. That big blob of blue binary data near the bottom are the raw bytes. There is the name of the server the client wants to talk to down by the 2nd red arrow and a bunch of bytes after it I obscured to hid the server's name, but suffice to say other than that name nothing else was understandable by humans without some secret decoder ring nearby.
So we know we are either in the heart of it or darn close... the next packet, #100, shows us this:
It is another TLS Exchange, and inside the text of the packet we can see the words "@SSL_Self_Signed_Fallback" and some ascii digits. So we know SQL Server is using some certificate it minted on its own to protect the communication channel, but again we have no idea what protocol it is using - is it using the one cracked by the ancient Romans in 4BC or UltraHighStrengthTitaniumNSAGiveMeAllYouveGotCrypto? Who knows?
Spoiler alert: Moving the traffic to port 443 so Wireshark uses the standard TLS dissector on the packets doesn't reveal anything. Been there, done that, got the t-shirt to prove it - take my word for it.
I knew that SQL Server did its little hello up front before negotiation of the secured socket, but had no idea it was doing things in its own way until I sniffed the data. The good news was this took all of two minutes to discover, so I didn't waste a whole lot of effort on it.
Back to searching
Meanwhile, back at various search engines, nothing definitive. The best possible answer was found here:
https://blogs.msdn.microsoft.com/sql_protocols/2007/06/29/ssl-cipher-suites-used-with-sql-server/
"When enabling channel encryption between the application and SQL Server, users may wonder what encryption algorithm is being used to protect their data. Unfortunately, this isn’t an easy question to answer and here’s why. SQL Server (both 2005 and 2000) leverages the SChannel layer (the SSL/TLS layer provided by Windows) for facilitating encryption. Furthermore, SQL Server will completely rely upon SChannel to determine the best encryption cipher suite to use."
The best answer I can find is from 2007 - that is 9 years ago - whose answer is "it depends..."
Surely in the last 9 years someone has come up with something more definitive, haven't they?
No, they haven't. (and don't call me Shirley.)
This is pretty much how every program running on a Windows platform operates - it calls down to SChannel to setup a secure communications link and SChannel does all the heavy lifting. If it didn't, every program would have to bring along all its own encryption and protocol algorithms and not many programs do this.
The blog article references a bunch of other articles that say what suites are supported. I also knew I'd disabled those obsolete protocols.
So I totally believed 100% that I was secure.
However, as Tom Cruise said in A Few Good Men, "It doesn't matter what I believe, it only matters what I can prove."
So, while I believe it 100%, would I be willing to bet my paycheck against yours that is truly how it operates? (For those who don't know me, that is my ultimate confidence level.)
No, I would not make that bet. My belief may have been at 100%, but my confidence that what I believed from everything else I was reading was also true wasn't at 100%.
And my client insisted that if I couldn't prove that I wasn't using an old, brittle, insecure SSL layer, then alternate paths to security would have to be taken. That client of mine - she can be a real ball-breaker - but she keeps me on my toes. Given time constraints and the answer I couldn't put my hands on quickly, we went down an alternate security path.
However, unsolved problems will not let me rest. During times when I was waiting for something else to finish, my research continued.
下面还有好多内容，看不完了
[RESOLVED] "The client and server cannot communicate, because they do not possess a common algorithm"
Abstract: If you have a application (e.g. IIS .net Application) which need to speak with an SQL server, you might get the error "The client and server cannot communicate, because they do not possess a common algorithm" when you try to perform a SQL connection.
The issue came up when we setup a Thycotic Secret Server on a hardened OS. On the OS TLS 1.0 was disabled for security reasons, however at the moment the used Microsoft SQL server didn´t speak TLS 1.1 or TLS 1.2. So the error message:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)
came up. After TLS 1.0 was enabled on the Thycotic Server the installation could be performed without issues. So make sure that your Microsoft SQL environment is up to date and supports TLS 1.1/1.2 if you wish to disable TLS 1.0.
Cross reference:
https://community.spiceworks.com/topic/860418-problem-with-ms-sql-after-disabling-ssl-3-0-and-tls-1-0
http://www.admin-enclave.com/en/articles/windows/151-enable-tls-1-1-1-2-on-windows-7-2008-r2.html