Broken SQL Server node – [sqsrvres] ODBC sqldriverconnect failed

I came across an issue that I wanted to share because I wasn’t able to find much on the web when I needed some pointers.

A bit of background info:

SQL Server 2005 Standard Edition (x64) 9.00.2035.00

Windows Server 2003 (Build 3790: SP2)

Active / Passive cluster.

The problem was that the SQL Server services wouldn’t start on node 1 and these are the errors from the Application Event Log:

00000ca0.000012ac::2011/03/29-10:19:36.983 ERR  SQL Server <SQL Server>: [sqsrvres] ODBC sqldriverconnect failed

00000ca0.000012ac::2011/03/29-10:19:36.983 ERR  SQL Server <SQL Server>: [sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 80090331; message = [Microsoft][SQL Native Client]Client unable to establish connection

00000ca0.000012ac::2011/03/29-10:19:36.998 ERR  SQL Server <SQL Server>: [sqsrvres] ODBC sqldriverconnect failed

00000ca0.000012ac::2011/03/29-10:19:36.998 ERR  SQL Server <SQL Server>: [sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 80090331; message = [Microsoft][SQL Native Client]SSL Provider: The client and server cannot communicate, because they do not possess a common algorithm.

The fix is relatively straight-forward but be aware that it requires a modification to the registry so back up the key first!

Here is the fix:

Backup the following -

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL

Then rename

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL to

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNELold

(You may get an error indicating that this key already exists. This is a common ‘feature’ and can be ignored).

Now fail the SQL cluster group over to the passive (broken) node and it should start without issue.

SQL 2005 “cluster build” common failures

Hello.

This is a quick post about the common failures we come across when installing SQL Server 2005 across a windows active/passive cluster.

Permissions on the service group

Make sure the account you are installing SQL Server under has the necessary permissions to add members to the Service Account Group. If you don’t have the required privileges you will encounter the following error -

“You do not have privileges to add accounts to the domain groups specified for this failover cluster. Ask your domain administrator for privileges to add new accounts to the domain groups, or log on using an account that does have permission”

Windows task scheduler is not running on the passive node

Now this is very subtle and still catches me out every now and then. During the installation phase you will just experience a very generic installation error.

Make sure that the Windows Task Scheduler is enabled and is running on the passive node otherwise because this is how the installation process installs the binaries from the active node on to the passive node.

Managing and analysing multiple trace files

Have you ever set off a profiler trace and then realised that you’ve amassed tons and tons of data and now you need to start searching for that needle in a haystack?

If the answer to this question is yes then you might be interested in a pretty cool SQL function that lets you read the trace files and, even better, import them into a table.

Consider the following SQL statement:

SELECT * INTO [db_name]..[table_name]

FROM ::fn_trace_gettable(‘D:\DBAScripts\output\sql_trace_file.trc’, default);

This will import your SQL trace files into a table, after which you can add indexes to optimise the numerous queries you will no doubt have to run against the data.

Let me just break down the syntax of that statement to try and give you a better understanding of how you can best utilise this feature.

The SELECT * INTO is straight-forward stuff so I won’t bother with that bit, let’s just concentrate on the function syntax.

::fn_trace_gettable is the actual name of the function we are using. The two parameters within the parenthesis basically specify the full path and file name and the number of trace files you want to import. By specifying default you are effectively saying that you want all of the trace files that have been created under that name. Multiple trace files of the same name will be suffixed with an underscore followed by a number, for example, a trace file called sql_trace.trc may have rolled over a number of times to create sql_trace_1.trc, sql_trace_2.trc etc. If you only want to import the first 5 trace files then just specify the number 5 instead of ‘default’. Simple.

So as you can see, you have a pretty handy little function that can import all of that trace data into a SQL table which you can then analyse the data until your heart is content.

 

My Windows “Share” cluster resource keeps failing

Have you ever had that annoying problem where you’ve created a cluster resource of type “share” (typically a shared folder on one of your SAN drives) that just keeps failing? If I had to pick a scenario I would chose log shipping from one cluster (Production) to another (Disaster recovery). So, you have your shared folder that contains your log backups but the resource keeps failing. Even worse, you’ve forgotten to un-check that box that affects the cluster group so that when the share fails it brings your entire group down? Yup, been there, done that. And if it’s a share that SQL needs access to then the chances are that you’ve put this share in your SQL cluster group and you’ve got a yo-yo’ing SQL Server service all because of a failing share.

First off, clear that check-box that reads “Affect the cluster group”. If your cluster share fails it’s really not that big a deal. Certainly not big enough to fail-over your cluster.

Secondly, I have a fix for you. You basically need to add in a key to your registry (ON BOTH NODES!! ) and here it is.

The registry path is HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\lanmanserver\parameters and you basically need to create a key called IRPStackSize and set the value to HEX 12.

That’s it. Simple.

Reboot the node (at a convenient time) and hey presto, no more failing service.

Remember to repeat on both nodes! 

 

SQL Server security bulletins

You’ve no doubt seen the security bulletins issued by Microsoft over the last few months and if, like me, you’ve had to apply them to your SQL Server estate you may well have had some cause for confusion.

I’ve been testing and all of the KB’s that I have to deploy and I’ve started rolling them out onto our production boxes. But how do you know which KB you need to apply to your SQL instance? Well it is actually explained on the Microsoft site but I’ve got a very quick “at a glance” version, which is the reason for this post.

This is my crib-sheet for security bulletin MS08-040

SQL Server Major Release	SQL Server 7.0 and MSDE 1.0	SQL Server 2000 GDR and MSDE 2000	SQL Server 2000 QFE and MSDE 2000	SQL Server 2005 GDR	SQL Server 2005 QFE
SQL Server Build Range	7.00.1063-7.00.1151	8.00.2039-8.00.2049	8.00.2148-8.00.2272	9.00.3042-9.00.3067	9.00.3150-9.00.3230
Patch To Apply	KB948113	KB948110	KB948111	KB948109	KB948108
New Build Number	7.00.1152	8.00.2050	8.00.2273	9.00.3068	9.00.3233

MS08-052 Security bulletin crib sheet to follow soon….

A further update to that out-of-date post, here are the latest build levels:

SQL 2008 R2      = 10.50.1765 = RTM with CU 6

SQL 2008        = 10.00.4279 = Service Pack 2 with CU 3

SQL 2005        = 9.00.5266  = Service Pack 4 with CU 3

SQL 2000        = 8.00.2282  = Service Pack 4 with KB960083

 

Note:

SQL 2005 SP4 becomes mandatory in January 2012 

SQL 2008 SP2 becomes mandatory in October 2011

Identifying job owners

This is a handy little function that will decrypt the owner_id from the msdb sysjobs table. Just wanted to post it very quickly because I’ve just had to search for it yet again.

SELECT name, suser_sname(owner_sid) as owner
FROM
sysjobs