March 2023
M	T	W	T	F	S	S
	1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

Archive for the ‘Connectivity’ Category

Solution : Connectivity Error – Provider: SSL Provider, error: 0 – The token supplied to the function is invalid

Posted by blakhani on November 14, 2014

I said earlier that I have bunch of *good* friends who remember me whenever they get any issue with SQL Server. So I got a call from my friend and he told that he is not able to connect to SQL Server from some client. Since it’s only few client, we can easily rule out the issue with SQL Engine not allowing connection. I asked him to connect via SQL Server Management Studio and got below error.

TITLE: Connect to Server
——————————
Cannot connect to SQLDBServer.
——————————
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 – The token supplied to the function is invalid) (Microsoft SQL Server, Error: -2146893048)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-2146893048&LinkId=20476
——————————
BUTTONS:
OK
——————————

Did basic troubleshooting as below

Tested basic connectivity like ping, telnet etc. everything is working fine, except the connection to SQL engine from this machine.
Error message says SSL but there was no certificate used in SQL Server.
There was no certificate used.

Next major task is to find out what is the difference in both servers. So I asked him what were the things done on “bad” client. He said that he applied bunch on Windows updates. Looking at list I found one which talks about SSL.

http://support.microsoft.com/kb/2643584 (MS12-006: Vulnerability in SSL/TLS could allow information disclosure: January 10, 2012)

We check the machines and found that SendExtraRecord was 1 on “bad” client whereas other “good” clients it was 2.

Here were the steps taken to solve the issue. (taken from KB)

Click Start, click Run, type regedit in the Open box, and then click OK.
Locate and then click the following subkey in the registry:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SecurityProviders\SCHANNEL
On the Edit menu, point to New, and then click DWORD Value.
Type SendExtraRecord for the name of the DWORD value, and then press Enter.
Right-click SendExtraRecord, and then click Modify.
In the Value data box, type 2 to disable the split record in schannel, and then click OK.
Exit Registry Editor.

In our case it was 1 and making it 2 fixed the issue and were were able to covert bad clients to good clients.

Hope this helps.

Cheers,
Balmukund

Posted in Connectivity, Error | Tagged: pre-login handshake, SQL, SSL Provider, The token supplied to the function is invalid | Leave a Comment »

Help: How to fix error – Reason: Server is in single user mode. Only one administrator can connect at this time

Posted by blakhani on December 6, 2013

First of all this is not normal to start SQL Server in single user mode. So if you are reading this blog by getting this as a search result, you might be in a tough situation. In some situations, like restoring system database or during disaster recovery you may need to start SQL in single user mode.

Okay, so you have started SQL Server in single user mode by specifying start-up parameter “m” either by net start MSSQLServer /m or via command prompt sqlservr.exe –m –sInstanceName but when you are trying to connect via any tool (SQLCMD, OSQL, SQL Server Management Studio or any other) you are welcomed by error message.

TITLE: Microsoft SQL Server Management Studio
——————————
Error connecting to ‘(local)\SQL2k8R2’.
——————————
ADDITIONAL INFORMATION:
Login failed for user ‘Contoso\demouser’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18461&LinkId=20476
——————————
BUTTONS
OK
——————————

Error message is clear that someone “else” has made connection to SQL Server and due to /m switch, SQL is not letting you get in. Now, logically there are two ways to fix this problem.

Find out who is connecting before you and stop that application (difficult in real/disaster time)
Ask SQL Server to not to allow anyone except me.

Second one sounds more easy. So let’s discuss that.

If you want to know the root cause of “why its in single user mode” then go back and look for SQL Error Log and you may find something as below.

In above Errorlog, we can see additional start-up parameter and warning that SQL is in single user mode.

Essentially we want to start in single use mode and no one else except you should be able to connect. Books online has explained this clearly that you can append m parameter with the client application name. Here are few example

Only SQLCMD should be able to connect then it would be m”SQLCMD”
If you want to use Management studio only then it would be m"Microsoft SQL Server Management Studio – Query".

Let’s see it in action. I can add start-up parameter in multiple ways. My favorite is staring SQL from command line as a service, My instance name is SQL2K8R2 so below would be the command

Net Start MSSQL$SQL2K8R2 /m”SQLCMD”

If you have default instance than it would be

Net Start MSSQLServer /m”SQLCMD”

Once you have started SQL Service in Single use mode then only SQLCMD application can connect and other connection would get error message.

Let’s see what we nave in ERRORLOG

2013-12-06 09:13:50.08 Server      Registry startup parameters:
     -d E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf
     -e E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG
     -l E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2013-12-06 09:13:50.08 Server      Command Line Startup Parameters:
     -s "MSSQLSERVER"
     -m "SQLCMD"

…

2013-12-06 09:13:50.80 spid4s      Warning ******************
2013-12-06 09:13:50.80 spid4s      SQL Server started in single-user mode. This an informational message only. No user action is required.

….

2013-12-06 09:14:32.93 Logon       Error: 18461, Severity: 14, State: 1.
2013-12-06 09:14:32.93 Logon       Login failed for user ‘Contoso\demouser’. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]

It’s important to note that string after –m parameter is case-sensitive. This means that if you give sqlcmd (all lower case) then connection can’t be made. If we want to use management studio then the parameter would be –m”Microsoft SQL Server Management Studio – Query”. This is the exactly same name as in program_name when you look at sys.processes or sys.dm_exec_sessions

Hopefully this blog would help you in making a connection to SQL Server without stopping application, changing password, disabling account as there were the tricks I have seen to get into SQL when only one connection can be made and unfortunately that’s not you.

Cheers,
Balmukund Lakhani
Twitter @blakhani
Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in Connectivity, Error, Screenshot, Step by Step | Tagged: disaster, only one administrator can connect at this time, restore master, Server is in single user mode, sql server, sqlcmd | 26 Comments »

Help : What’s my SQL Server Name?

Posted by blakhani on June 19, 2011

“What’s in a name? That which we call a rose by any other name would smell as sweet.” – William Shakespeare

Well, SQL Server connectivity did not know about Shakespeare Hot smile While working on the forum, one of the most common error posted is below:

TITLE: Connect to Server
——————————
Cannot connect to <Shakespeare>.
——————————
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
——————————
BUTTONS:
OK
——————————

Let’s look closely at the error message.
1. The server was not found or was not accessible.
2. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
3. error: 26 – Error Locating Server/Instance Specified.

All of the above are shouting out loud that “Damn! you have given wrong name to connect”. If you have given right name then check if SQL is up and running and then whole troubleshooting starts. The purpose of this post is to help the beginners who don’t care about troubleshooting because they are Visual Studio developers and they are connecting to local SQL Express most of the time.

Okay my dear developers, don’t worry… take a deep breath and start…

To get the SQL Server Name, the first thing you need to do is to find the instance name. On a single machine you can install 16 instances of SQL Servers. Each would have some name. To find the instance name of a SQL Server instance, please follow the steps below:

Open SQL Server Configuration Manager (SSCM) from Start – > All Programs -> Microsoft SQL Server 2005/2008/2008 R2 -> Configuration Tools.
In the SSCM, click SQL Server Services under SQL Server Configuration Manager (Local), you could find list like SQL Server (InstanceName),
The InstanceName is what we need to find, instance name. If you install multiple instances on the server, you will see more than one SQL Server (InstanceName) listed.

In above picture you can see two “SQL Server” in “Service Type” column. It means I have two instances of SQL Server. For default instance you would see Name as SQL Server (MSSQLServer). It means on my machine I have two “named” instances of SQL Server. Keep in mind that you can have only only default instance on a machine.

Okay great! Now you know that your machine has default instance or named instance. And if it’s named then you know its name. Correct? Now lets quickly see what name you should use to connect. Before that you need to find hostname or computer name. Go to Start > Run > cmd This would open command prompt. Type hostname and you should see your machine name as below.

Default Instance	Named Instance	Comments
BLAKHANI	BLAKHANI\SQLEXPRESS BLAKHANI\SQL2K8R2	this would work from remote machine also.
(LOCAL)	(LOCAL)\SQLEXPRESS (LOCAL)\SQL2K8R2	local only
.	.\SQLEXPRESS .\SQL2K8R2	local only
LOCALHOST	LOCALHOST\SQLEXPRESS LOCALHOST\SQL2K8R2	local only
BLAKHANI.MYDOMAIN.COM	BLAKHANI.MYDOMAIN.COM\SQLEXPRESS BLAKHANI.MYDOMAIN.COM\SQL2K8R2	Remote connections should be allowed as mentioned in http://support.microsoft.com/kb/914277 ( How to configure SQL Server 2005 to allow remote connections)
TCP:221.222.223.224,1433	TCP:221.222.223.224,9999	IP and Port can be found by reading ERRORLOG of SQL Server.

If you don’t see configuration manager then you can also get the instance name from services applet. To do this, go to Start > Run > Services.msc
Here is what i see on my screen

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in Connectivity, SQL Server | 58 Comments »

Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Blog Stats

Blogroll

Select GETDATE()

Subscribe

Follow Blog via Email

Date < getdate()

Select location from world

Meta

Archive for the ‘Connectivity’ Category

Help: How to fix error – Reason: Server is in single user mode. Only one administrator can connect at this time

Help : What’s my SQL Server Name?