CODEWITHSUNDEEP
sql-serverdatabase-connectionkerberosintegrated-security
KrasshX
KrasshX

Reputation: 142

SQL Server 2012 - Distant connection issues with Integrated Security

I have two SQL Servers : one in Dijon, France; one in Arvada, Colorado, US. A data replication has been set up between them.

Everything works fine when users try to connect to SQL Server 2008 R2 Dijon database.

BUT an error occurs when one of my users is trying to connect to SQL Server 2012 US database from a VB.Net application.

The error is :

"A connection was successfully established with the server, but then an error occurred during the login process (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

This error shows up only for one user. The others can connect without any issue. I have maybe a clue about this. We already encountered connection issues for him, because he belonged to many AD security groups.

Here is the connection string :

Data Source=server\instance;Initial Catalog=db;Integrated Security=SSPI;Connection Timeout=0;

We have the same issue for a US user trying to connect to the same server. But not when he tries to connect to Dijon. Do you have any clue that could help me resolving this issue please ?

I checked StackOverflow threads and other solutions from the web, but nothing helped me...

Upvotes: 0

Views: 750

Answers (2)

KrasshX
KrasshX

Reputation: 142

It was, as I expected, a problem of Kerberos authentication because my user has too many AD security groups... The solution is explained here : Problems with Kerberos authentication when a user belongs to many groups and here MaxTokenSize and Windows 8 and Windows Server 2012

If the authorization data for a user attempting to authenticate is larger than the MaxTokenSize, then the authentication fails for that connection using that protocol.

On Windows 7 and Windows Server 2008R2 , the MaxTokenSize (the default buffer size for Kerberos) is 12k. Its size has been increased in Windows 8 and Windows Server 2012 to 48k. It wasn't enough for my case.

I had to add a key in the registry HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters\MaxTokenSize (REG_DWORD type to the decimal value 65535).
Then I rebooted the server.

My user can now access data without error.

Upvotes: 0

Gehan Fernando
Gehan Fernando

Reputation: 1267

Refer following URLs

Troubleshooting: Connection Forcibly Closed

TCP Provider, error:

connection was forcibly closed

Follow these steps

  1. First goto services and check whether 'SQL Server Browser' service is started, if not start the service.

  2. Open SQL Server Configuration Manager

  3. Goto Protocols for MSSQLSERVER
  4. Enable all protocols
  5. Goto SQL Native Client
  6. Select Client Protocols and and Enable All
  7. Restart the SQL Server services.

Use .. (.NET Framework Data Provider for SQL Server) as provider

Connectionstring

  1. Data Source=server\instance;Initial Catalog=;Persist Security Info=True;User ID=;Password=;Network Library=dbmssocn

Upvotes: 0

Related Questions