May 7, 2015 at 1:59 pm
Scenario: a stored procedure is executed in database ABC. The procedure includes a MERGE statement with objects in the ABC and DEF databases. A Windows login is executing the procedure. Contained users is not enabled anywhere on the instance. And the user is not orphaned in either database, i.e. the login is associated to the user correctly by-sid in each one.
Of course, the Windows login must be authenticated to access database ABC, and authorized to execute the procedure. That is happening. Most of the time, so is the MERGE statement. But every once in a while we're seeing an odd "Network error code 0x2746 occurred while establishing a connection" message. The procedure has a TRY/CATCH retry loop around the MERGE statement, so it enters that loop. Re-read: the procedure is still running, the network error wasn't about the client connection executing the procedure. So what was it about?
Does that MERGE statement, accessing database DEF, require SQL to authenticate the Windows login again, because it's a different database (even though it's on the same instance, and was already authenticated for ABC)? There shouldn't be any other network activity involved in that phase of the execution. Plus, when the process had been using a SQL Server-authenticated login, this never happened.
But before I go blaming our DCs or network... I need to know, are they in fact involved here, or not? I believe they are, but... never had to care enough before to really know. 😉 I'm trying to stave off the "let's switch it back to a SQL login" demand that's sure to come soon, if I can't find an appropriate resolution to this.
May 7, 2015 at 2:16 pm
caddywonkus (5/7/2015)
Scenario: a stored procedure is executed in database ABC. The procedure includes a MERGE statement with objects in the ABC and DEF databases. A Windows login is executing the procedure. Contained users is not enabled anywhere on the instance. And the user is not orphaned in either database, i.e. the login is associated to the user correctly by-sid in each one.Of course, the Windows login must be authenticated to access database ABC, and authorized to execute the procedure. That is happening. Most of the time, so is the MERGE statement. But every once in a while we're seeing an odd "Network error code 0x2746 occurred while establishing a connection" message. The procedure has a TRY/CATCH retry loop around the MERGE statement, so it enters that loop. Re-read: the procedure is still running, the network error wasn't about the client connection executing the procedure. So what was it about?
Does that MERGE statement, accessing database DEF, require SQL to authenticate the Windows login again, because it's a different database (even though it's on the same instance, and was already authenticated for ABC)? There shouldn't be any other network activity involved in that phase of the execution. Plus, when the process had been using a SQL Server-authenticated login, this never happened.
But before I go blaming our DCs or network... I need to know, are they in fact involved here, or not? I believe they are, but... never had to care enough before to really know. 😉 I'm trying to stave off the "let's switch it back to a SQL login" demand that's sure to come soon, if I can't find an appropriate resolution to this.
Capture the relevant extended events, dm_exec_sessions, failed logins (OS&SQL) etc. and analyse. Further, is it a double hop issue, do you need to set up a linked server or use Kerberos?
😎
May 7, 2015 at 2:44 pm
There are no linked servers involved. They're not failed logins into SQL Server; it's configured to log those, and none appear. The system_health XE session is where we're seeing the error in the first place - it's not appearing in SQL Server's regular log. It does appear in the XE session every time the procedure in question ends up having to enter its CATCH block and loop/retry.
I just want to know - when this two-database query runs, with both databases on the same server, does SQL have to authenticate the connection to the second database even though it's on the same instance? That's all I wanna know, because there's no other reason I can think of why SQL Server would be reaching outbound over the network at that point in the execution. Again - no linked servers, no external resources, just two databases on the same instance, and the login has already been authenticated upon connecting to the first database.
May 7, 2015 at 9:58 pm
Sorry I misread the question. The server will check the permissions of the login account in the second database unless Cross-database ownership is enabled and it is the owner of the objects in question in both databases.
😎
May 8, 2015 at 7:28 am
Thanks! That's what I figured, so most likely those sporadic Network errors during the proc call are authentication-related. I've seen a few references to buggy NIC drivers, too, but point is yes, that's what it's hitting the network for in the first place. Before I go down a rabbit hole, I like to make sure I'm at least chasing the right rabbit. 😛
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply