SQL Server Client Connection Detail

I already explain how to check the connection mode in the SQL on server side.

Now, in this article, I will discuss the details client-side connection to SQL Server using the SQL Enterprise Manager’s (SQL EM) New SQL Server Registration wizard.

The purpose of this article is to provide more information about the mechanisms of SQL Server Enterprise Manager in handling the first time connection to the SQL Server using the New SQL Server Registration Menu, and also to shed some light about the detail about how the SQL EM client connects to the remote server.

As you may already know, the new SQL Server Registration Menu is accessed by clicking the “SQL Server Group”, then perform right-click to activate the New SQL Server Registration Menu :

This usually will bring up the SQL Server Registration wizard dialog box. This user interface is handled using SEMSFC.DLL (Starfighter Foundation Class), using the SFCWndProc procedure.

First step wizard will instruct the user to choose the server name, next it will try to connect the server name specified by the user.

For the purpose of learning the client connection mechanism, let’s try to connect to the non existent server, so that it will give the error :

As I am understanding so far, the client connection to the SQL Server is ultimately handled by a set of API called the Net-Library API.

There are many references about the theory of client connection to the SQL server, but I will narrow the scope to the fact and practical aspects about this subject.

The SQL Enterprise Manager is using Net-Library API in the form of DBNETLIB.DLL, usually in C:\WINDOWS\SYSTEM32 folder.

The first-time connection is handled by ConnectionOpen routine. This is actually the stub for doing some conversion to reconciliate between the unicode character version and non-unicode. It will then call its internal _ConnectionOpen routine.

This routine then checks to the HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\ProtocolOrder value to determine method of connection.

The default value of this registry value is “tcp np”. This means, the connection routine will try the TCP/IP protocol, then if not succeded, it will try the Named-pipe protocol.

Other codes including (from MSDN Documentation) :

spx (IPX/SPX)
adsp (AppleTalk)
rpc (MultiProtocol through RPC)
vines (Banyan VINES)

Some connection problems that originated from client side can be traced to the value of this registry value. So, try to change this value when experiencing the connection problem, after make sure that the server side connection is already checked and functioned normally.

Please remember that in order that these changes will take effect, close the SQL EM and re-start it so it can reflect the revised value.

Alternatively, you can also change this value using the SQL Server Client Network Utility :

After performing some changes, re-start the SQL EM, and try to re-connect to the SQL Server.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: