Can't access an SQL instance locally on the server

Question

Can't access an SQL instance locally on the server

Tony Mourad 81

Hi to all, i am toni, i am a system engineer but right now i am playing a role of db admin inside the company because our dbadmin left :).i have a windows server 16 where sql server 2016 installed on it. 5 instances exist on the server all working fine except one instance, i can't access to it using a credentials provided by the ex dbadmin. I am trying to access the instance locally from the server using sql management studio. When trying to access i got the below error A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64) right now i am not able to access the instance, the maintenance plan failed daily and i am not able to do anything. Can you advise please about the error message above and why i am not able to access it. Thanks and regards,

Accepted answer

3 additional answers

Your answer

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

Hi, i saw on event viewer that the connection allowed to this instance is 5, i guess maybe this is the problem.

Yes, exceeding that setting produces exactly that error message; I just tested.

Thankfully, there is an easy way to resolve the issue. When you connect to the server, prepend ADMIN: to the server name. This will log you in on the admin connection, which does not count against that configuration setting. You may still get an error when you connect, because SSMS may try to set up a second connection for Intellisense, but that will not work out. (Because there can only be one session on the Admin connection.)

Once you are connected, you can run

EXEC sp_configure 'user connections', 0
RECONFIGURE WITH OVERRIDE

You need to restart SQL Server for the change to take effect.

Of course, it is an interesting question, why the setting is set to five. Normally, you should not touch this setting, as SQL Server controls this itself. And 5 is a ridiculously low number. One reason I can think of is that there is a third-party software running on this machine, and the setting is there to enforce a license of five users.

I have a question, as the backup maintenance plan failed on this instance and can't check it as i can't access the instance, so i stop the services of the instance and i copy the mdf and ldf files to another location. Can i consider this method as backup until i resolve the login issue?

Yes, that works. But you could also use the ADMIN connection in the maintenance job.

Tony Mourad 81 Reputation points

2023-02-12T13:03:15.85+00:00

Dear Erland, And thank you for your help. As my backgroud is not a DBA can i ask you what do you mean by this : "When you connect to the server, prepend ADMIN: to the server name. This will log you in on the admin connection, which does not count against that configuration setting.". I explained before that there is no way to access the instance, can you explain your solution to me step by step please?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-02-12T13:14:55.4033333+00:00

Rather than specifying SERVER\INSTANCE in the connection dialog you say ADMIN:SERVER\INSTANCE. Assuming that you are on the machine where SQL Server is running, you will be able to connect. This is possible because SQL Server permits for one single connection on the so-called admin connection. And which, by default, is only available locally.
Tony Mourad 81 Reputation points

2023-02-12T15:19:41.2433333+00:00

I receive this error : dedicated administrator connections are not supported via ssms as it establishes multiple connections by design
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-02-12T16:10:09.31+00:00
Which version of SSMS is that? I have never had any problems with connecting to the admin connection on SSMS.

Anyway, you can also open a command-line window and say:

sqlcmd -A -S server\instance

(-A specifies the admin connection.) At the prompt you get you enter: "1>" etc is the prompt you will get.

1> EXEC sp_configure 'user connections' 5 2> RECONFIGURE WITH OVERRIDE; 3> go 1> exit

and then restart SQL Server.
Tony Mourad 81 Reputation points

2023-02-12T16:47:50.7066667+00:00

Dear Erland and thank you for your help really apprciated. I will try your suggestions tomorrow after working hours, but i have a question concerning this statement : EXEC sp_configure 'user connections' 5 the number should 5 or 0? 1> EXEC sp_configure 'user connections' 0

2> RECONFIGURE WITH OVERRIDE;

3> go

1> exit
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-02-12T18:40:42.8133333+00:00

Sorry. Yes, you are correct. The number should be 0.
Tony Mourad 81 Reputation points

2023-02-14T10:46:36.76+00:00

Dear Erland, i just want to thank you for all your help and suggestions, you helped me solved my issue. Thanks a lot.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-02-14T21:49:47.88+00:00

I am only glad to hear that it worked out!

Answer 2

ERIC MILA 5

Hi Toni,

How do you connect : with Active Directory rights or SA account ?

Have you tried from your device ? or the device of your old DBADMIN.

Maybe :

the user account of you old DBADMIN was Admin of SQL SERVER and other standard account are disable.
a restriction of client or Ip adresses to manage SQL SERVER is implemented

About the maintenance plan, see Windows services for SQL Server. You can find something.

I hope bring some help to you

Regards

Tony Mourad 81 Reputation points

2023-02-11T17:47:53.9833333+00:00

Hi, i saw on event viewer that the connection allowed to this instance is 5, i guess maybe this is the problem. I have a question, as the backup maintenance plan failed on this instance and can't check it as i can't access the instance, so i stop the services of the instance and i copy the mdf and ldf files to another location. Can i consider this method as backup until i resolve the login issue?

Answer 3

Erland Sommarskog 121.4K MVP Volunteer Moderator

Open SQL Server Configuration Manager. There are two things to check here. One is the node SQL Server Network configuration. Check that Shared Memory is enabled:

User's image

Shared memory is what you expect to be used for a local connection, but the error message talks about TCP.

Then go the Services node, and find the instance in question. Double-click it and go to the StartUp parameters tab and select theh -e option:

User's image

The argument for this option gives you the location to the errorlog for this instance. This file may include messages that are related to the login failures.

Tony Mourad 81 Reputation points

2023-02-11T17:56:05.69+00:00

Hi, i saw on event viewer that the connection allowed to this instance is 5, i guess maybe this is the problem. I have a question, as the backup maintenance plan failed on this instance and can't check it as i can't access the instance, so i stop the services of the instance and i copy the mdf and ldf files to another location. Can i consider this method as backup until i resolve the login issue?

Answer 4

Hi @Tony Mourad

Some workarounds:

Try to set TrustServerCertificate=True in your connection string. See: Breaking Changes
Make sure that TCP/IP is enabled as a client protocol on the application server and in case of issue try upgrading to/enabling TLS 1.2 else to tls 1.0
Check the port number to be 1433 and make sure the server name is fully qualified in the connection string.
Try increasing the connection time out to a larger value.

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

Can't access an SQL instance locally on the server

3 additional answers

Your answer