MSSQLWIKI

Karthick P.K on SQL Server

Archive for the ‘Connectivity’ Category

The connection to the primary replica is not active. The command cannot be processed

Posted by Karthick P.K on June 20, 2013

When you configure SQL Server always on available group from management studio it may fail with below error while joining secondary replica to the availability group.

 

Error 1

 

{

Joining database on secondary replica resulted in an error.  (Microsoft.SqlServer.Management.HadrTasks)

——————————

ADDITIONAL INFORMATION:

Failed to join the database ‘AG’ to the availability group ‘AG1’ on the availability replica ‘NODE2’. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

The connection to the primary replica is not active.  The command cannot be processed. (Microsoft SQL Server, Error: 35250)

}

 

Error 2

 

{

TITLE: Microsoft SQL Server Management Studio

——————————

Failed to join the instance ‘NODE2’ to the availability group ‘AG1’. (Microsoft.SqlServer.Management.SDK.TaskForms)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476

——————————

ADDITIONAL INFORMATION:

Failed to join local availability replica to availability group ‘AG1’.  The operation encountered SQL Server error 41106 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command. (Microsoft SQL Server, Error: 41158)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=41158&LinkId=20476

}

 

 

You may get below error when you configure AG availability group using  alter database command mentioned below or synchronization might fail with 35250 error mentioned below.

 

ALTER DATABASE [AG] SET HADR AVAILABILITY GROUP = [Group name];

 

Error 1

 

Msg 35250, Level 16, State 7, Line 1

The connection to the primary replica is not active.  The command cannot be processed.

 

 

To resolve  above errors

 

1. Ensure always on endpoint ([Hadr_endpoint]) are not blocked by firewall (Default port 5022).

 

2. Make sure startup account of primary server is added to all secondary server’s and Startup accounts of all secondary servers are added to primary servers.(Startup account of each replica to be added to other replica’s)

 

3. If log on account of SQL Server is “Nt service\” or local system account then ensure system account (Domainname\systemname$) of each replica is added to other replicas.

{

CREATE LOGIN [MSSQLWIKI\node2$] FROM WINDOWS

}

 

4. Grant connect on always on endpoints created on each replicas for startup account of other replica servers (Grant connect on endpoints even if startup account of other replicas are added as sysadmins).

{

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MSSQLWIKI\node1$]

}

 

5.  Make sure SQL Server name (select @@servername) matches with hostname.

6. Make sure cluster service startup account is part of SQL Server logins (More details in This link).

 

 

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights

 

Posted in Always On, Configuration, Connectivity, Security, SQL General | Tagged: , , , , , , | 28 Comments »

SQL Server monitor

Posted by Karthick P.K on May 11, 2013

Every SQL Server DBA would have faced situations similar to SQL Server not accepting connections for few minutes, SQL Server not responding for few minute or Applications not able to connect with SQL Server for few minutes. Before DBA’s gets alerted about the situation and starts troubleshooting the issue everything becomes normal. Challenge in this situations is it becomes very difficult to understand where the underlying problem was, It could be a network connectivity, Application server problem or It might be an issue with SQL Server itself. How do we collect diagnostic data to prove that SQL Server was stable at the time of issue (or) If the issue is with SQL Server then how to collect data we need for diagnosing the issue?

 

You can use SQL Monitor to monitor SQL Server instances

SQL Server Monitoring exe monitors the SQL Server services and creates diagnostic data and memory dump if SQL Server service is down (or) If SQL Server is not accepting connections (or) If SQL Server is not responding to Queries

How it works?

SQL Monitor checks the SQL Server in 3-Phases

1. Check the status of all the SQL Server service through the windows service control manager every 60 seconds.

2. If the service is running then check if SQL Server is accepting connections every 60 seconds.

3. If SQL Server is accepting Connections then probe to perform a simple query and see if SQL Server is responding properly.

4. If any of the SQL Server is not accepting Connections then connect to SQL Server using DAC, take a filtered stack dump which will be stored in errorlog directory of the instance , executes custom diagnostic script (c:\sqlmonitor\failoveranalysis.sql) and stores the output in c:\SQLmonitor\ with name “Servername+instancename.txt” which can be used to identify if there is any issue in SQL Server.

5. Once dump is taken release the DAC connection and wait for some time before we attempt to connect again. If connection is successful during subsequent attempt SQLMonitor.exe will continue monitoring the instance but if the connection fails again a new dump is generated and new diagnostic data is collected and appended to Servername+instancename.txt file in SQLMonitor folder.

There will be a gap of X minute between each diagnostic data and stack dump collection when the issue is continuing where X is (Number of Diagnostic data/dump already collected for this instance  * Number of Diagnostic data/dump already collected for this instance)

 

How to Configure?

1. Create a folder called SQLMonitor in C:\

2. Create a Text file called serverlist.txt  and enter all the SQL Servers in your environment to be monitored in below format.

Format:

Servername [TAB] Servicename;

Ex:

Server1 MSSQLServer;

Server2 MSSQL$Prod;

3. Invoke command prompt and open SQLmonitor.EXE.

Advantage:

1. Multi-threaded each server and service is verified using its own thread so retrieving information from one server will not affect the pooling interval to other server.

2. Single exe can be scaled to monitor more than 1000 servers and 1000 services.

3. Uses few MB of memory and system resources.

Requirements:

1. This exe can be invoked from any of the client systems with SQL Server client tools and SQL native clients installed.

2. Remote DAC connection has to be enabled in SQL Servers which are monitored.

3. EXE should be invoked under credential of user who has access to all the SQL Servers which are monitored and permission to view service control manager of windows servers in which SQL Server is running.

You can Download SQLMonitor.exe from this link

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights

Posted in Connectivity, SQL Server Engine, SQL Server Tools | Tagged: , , | 4 Comments »

SQL Server generated Access Violation dumps while accessing oracle linked servers.

Posted by Karthick P.K on August 8, 2012

 

When you run queries against Oracle linked servers from SQL Server you see errors like one below and access violation dumps are generated (SQLDump00XX.mdmp files in SQL Server error log folder).

{

External dump process returned no errors.
Using ‘dbghelp.dll’ version ‘4.0.5’
SqlDumpExceptionHandler: Process 510 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
*  Exception Address = 000000007752485C Module(ntdll+000000000002285C)

*   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION

*   Access Violation occurred reading address 0000041EA9AE2EF0

* Input Buffer 510 bytes –

}

 

To analyze the dump download and Install Windows Debugger from This  link

1. Open Windbg

2. Choose File menu –> select Open crash dump –>Select the Dump file
(SQLDump000#.mdmp)

3. on command window type    
.sympath srv*c:\Websymbols*http://msdl.microsoft.com/download/symbols;

4. Type .reload /f and hit enter. This will force debugger to immediately
load all the symbols.

5. Type .ecxr

6. Type  kL    and look at the stack

 

{

ntdll!RtlpFreeUserBlock
ntdll!RtlFreeHeap

}

If you see above frames in the top of the stack and if you are using Oracle Provider for OLE DB – Version: 11.2.0.1 and later.

There is a known issue with Oracle Provider for OLE DB – Version: 11.2.0.1 and later   when  — STYLE COMMENTS are used in linked server queries ,SP’s Etc .

Resolution

1. Remove the – -style comments
OR
2) use /* */ for the comments instead of —

 

If you liked this post, do like us on FaceBook at https://www.facebook.com/mssqlwiki and join our FaceBook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

 

Regards

Karthick P.K

Posted in Connectivity, Debugging, SQL Server Engine | Tagged: , , , , | 4 Comments »

SQL-Server resource fails to come online IS Alive check fails

Posted by Karthick P.K on January 31, 2012

SQL-Server resource fails to come online with below Error:

[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 35; message = [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible.

Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

 

Resolution:

Look at the version of (c:\windows\system32\sqsrvres.dll) and install the same version of SQL Server native client.

Cause:

When Higher version of SQL-Server is installed on a cluster in which lower version of SQL Server is already installed, the lower version SQL Server Resource DLL (c:\windows\system32\sqsrvres.dll) is upgraded to higher version and Higher resource DLL will be loaded by the resource monitor process to monitor Lower version as well.

For example: The Denali SQL Server Resource uses SNAC 11.0 to connect to the SQL instance and because SNAC 11.0 can be used to connect to Shiloh, Yukon and Katmai as well this side by side configuration will work. However if Denali is uninstalled, the Denali SQL Server resource DLL is not downgraded to Katmai, Yukon or Shiloh version and hence care should be taken to not uninstall SNAC 11.0 otherwise Yukon or Shiloh instance cannot be brought online.

Similarly When we install Yukon and Shiloh together, Yukon SQL Server Resource uses SNAC to connect to the SQL instance and because SNAC can be used to connect to Shiloh as well this side by side configuration will work. However if Yukon is uninstalled, the Yukon SQL Server resource DLL is not  downgraded to Shiloh version and hence care should be taken to not uninstall SNAC otherwise Shiloh instance cannot be brought online.

 

If you liked this post do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group MSSQLWIKI

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer
The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in Configuration, Connectivity, SQL General, SQL Server Cluster | Tagged: , , , , | 8 Comments »

Linked server connection fails with “An error occurred during decryption”

Posted by Karthick P.K on January 9, 2012

We might get Error: 15466, Severity: 16, State: 2  An error occurred during decryption while installing Projects servers (or) Sending mails using database mail (or) Linked server connections might fail with Msg 15593, Level 16, State 1, Line 1

Linked server connection fails with below error

{

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
An error occurred during decryption. (Microsoft SQL Server, Error: 15466)

Msg 15593, Level 16, State 1, Line 1

An error occurred while decrypting the password for linked login ‘distributor_admin’ that was encrypted by the old master key. The error was ignored because the FORCE option was specified.

}

Database mail might fail with below error

{

Set mail server login password failed for MailServer ‘Domain’.  (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

An error occurred during decryption. (Microsoft SQL Server, Error: 15466)

}

Or

You notice below errors in SQL Server errorlogs

spid10s Error: 15581, Severity: 16, State: 3.
Please create a master key in the database or open the master key in the session before performing this operation.

Cause

SQL Server service account was changed from services control manager (or) service master key was not backed up and restored when migrating SQL Server to another computer domain.

{

http://msdn.microsoft.com/en-us/library/ms187788.aspx

To change the SQL Server service account, use SQL Server Configuration Manager. To manage a change of the service account, SQL Server stores a redundant copy of the service master key protected by the machine account that has the necessary permissions granted to the SQL Server service group. If the computer is rebuilt, the same domain user that was previously used by the service account can recover the service master key. This does not work with local accounts or the Local System, Local Service, or Network Service accounts. When you are moving SQL Server to another computer, migrate the service master key by using backup and restore.

The REGENERATE phrase regenerates the service master key. When the service master key is regenerated, SQL Server decrypts all the keys that have been encrypted with it, and then encrypts them with the new service master key. This is a resource-intensive operation. You should schedule this operation during a period of low demand, unless the key has been compromised. If any one of the decryptions fail, the whole statement fails.

The FORCE option causes the key regeneration process to continue even if the process cannot retrieve the current master key, or cannot decrypt all the private keys that are encrypted with it. Use FORCE only if regeneration fails and you cannot restore the service master key by using the RESTORE SERVICE MASTER KEY statement.

}

 

Resolution

Regenerate the service master key using ALTER SERVICE MASTER KEY REGENERATE

If you receive the following error message when running ALTER SERVICE MASTER KEY REGENERATE.

{

The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.

}

We are left with only option to force regenerating service master key using “ALTER SERVICE MASTER KEY FORCE REGENERATE “.

Note:The service master key is the root of the SQL Server encryption hierarchy. The service master key directly or indirectly protects all other keys and secrets in the tree. If a dependent key cannot be decrypted during a forced regeneration, the data the key secures will be lost.

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Posted in Configuration, Connectivity, Security, SQL Server Tools | Tagged: , , , , , , , , , , | 9 Comments »

We do not see the SQL server and SQL Agent status from management studio. When we right click the instance, start, stop and resume options is disabled.

Posted by Karthick P.K on June 20, 2010

From  management studio we do not see the SQL server and SQL Agent status. When we right click the instance, start, stop and resume options is disabled.

 

1. Check if are running the management studio with elevated permissions (Right click SSMS—>Run as administrator)

 

2. WMI Permission could be a cause. Enable below permission in WMI.

 

–Open Dcomcnfg

–Expand Component Service -> Computers -> My computer

–Right-click and go to the Properties of My Computer

–Select the COM Security Tab

–Click on "Edit Limits"

–Under launch and activation permissions : Add both Startup account of SQLServer service and Logged on windows account.

 

 

–On the Start menu on, click Run.

–Type wmimgmt.msc, and then click OK.

–In the Windows Management Infrastructure program, right-click WMI Control (Local), and then click Properties.

–In the WMI Control (Local) Properties dialog box, select Security tab, expand Root, and then click CIMV2.

–Click Security to open the Security for ROOT\CIMV2 dialog box. Add startup account of SQLServer service and Windows Logon account and select Remote Enable permission.

 

 

3. Use the following command to give rights to query service .

 

subinacl /SERVICE MSSQL$SQLINST /GRANT=everyone

 

NOTE: MSSQL$SQLINST > Replace With your SQLServer service name.

 

 

4. We can use below command to reset the Security policy to default.

 

secedit /configure /cfg C:\WINDOWS\inf\defltsv.inf /db defltsv.sdb /verbose

 

–>If you still have the same error even after running this.

 

5. We can ran the following WMI query to check if we get any errors:

Select * from Win32_service where name="SQLServiceName"

 

 

Steps to check SQL Service status from WMI:

–Open “WBEMTEST”

–Connect to    “root\cimv2”

–Click on notification Query

–Run the below query

–SELECT * FROM __InstanceModificationEvent WITHIN 10 WHERE TargetInstance isa ‘Win32_Service’

–Stop SQLAgent or SQLServer service

–check if you get notification query:

 

 

6. Run the below Port query and check  if any of Instance is misidentified as cluster

 

a. "IsClustered "    Should be no

b.Check browser properties to see if it Standalone

 

7.Compare the HOST name ,select serverproperty (‘servername’)  , and select @@Servername

All the above three has to match.

–> If query “select serverproperty (‘servername’)” returns wrong name

 

Check the environment variable’s and see if we have below variables defined. Below keys should be present only for clustered SQLServer and  Key  "_CLUSTER_NETWORK_NAME_" should have SQLVirtual server name in case of cluster and this Environment variable should not be present for Standalone instance.

 

_CLUSTER_NETWORK_DOMAIN_=XXXXXXXXXXXX

_CLUSTER_NETWORK_FQDN_=XXXXXXXXXXXX

_CLUSTER_NETWORK_HOSTNAME_=XXXXXXXXXXXX

_CLUSTER_NETWORK_NAME_=XXXXXXXXXXXX

 

Note: Environment variable will be defined for service under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\MSSQL$InstanceName\Environment

 

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and

Posted in Connectivity, SQL Server Tools | Tagged: , , , | 3 Comments »

Configuring SSL for SQL Server using Microsoft Certificate Authority Server

Posted by Karthick P.K on June 12, 2010

Configuring SSL for SQL Server using Microsoft Certificate Authority Server 

Refer attached document  for detailed steps

1. Install IIS Server from ADD/Remove Windows Components (if it is not installed already)

2. Install Certificate Server ADD/Remove Windows Components (if it is not installed already)

3. OPEN Certsrv browser console by either of below mentioned ways,

A.  IIS Manager and browse to Machine Name — Web sites — CertSrv
B.  IE open
http://localhost/certsrv
C.  From IE open
http://<machinename&gt; /certsrv
e.g., http://pjhome1/certsrv

4. To Install CA (Root) Certificate

A. Click on ‘Download a CA Certificate, Certificate Chain, or CRL’

B. Click on Install this CA certificate chain

C. Click YES

D. CA chain (Root Certificate) installed successfully

5. Create a SERVER Side Authentication Certificate

A. Go to Certsrv site and click on ‘Request a certificate’

B. Click on ‘Advanced Certificate request’

C.Cick on ‘Create and submit a request to this CA’

D. Enter the certificate information

  • 1. Type the FQDN (Fully Qualified Domain Name) for the name
  • 2. Select ‘Server Authentication Certificate’ for Type of Certificate Needed.
  • 3. Check the ‘Mark Keys as exportable’ option
  • 4. Click on Submit

E. Click on YES to complete

F. We need to make a note of the ‘Request Id’ from the below screen.

6. Issue the certificate.

A. In MMC add ‘Certificates’&’Certificate Authority’ using ‘Add/Remove Snap-in’ options.

B. Click on ‘Pending requests’ in ‘Certificate Authority’.
(We would see certificate with Request ID which we generated in STEP 5.i.e., 7 here)

C. Right click on the certificate –> All Tasks –> ISSUE

D. Now we should see the certificate under ‘Issued Certificates’

7. Install the certificate
A. Click on ‘View the status of a pending certificate request’

B. Click on the certificate.

C. Click on ‘Install this certificate’

D. Click on YES

E. We will see the successfully installed screen.

8. Assign the certificate to the SQL Server instance.

A. Open SQL Server Configuration Manager
B. Right click on ‘Protocol on <instance name>’
(for the instance which we need, here it is STANDARD)

C. In the certificate tab and select the certificate we created earlier.

D. Click on Apply and restart the SQL Server instance to get this change applied.

9.After the successful deployment of the certificate (Server side) we should see the below message in our SQL Error Log file during the server startup.
The certificate was successfully loaded for encryption.

 

Regards

Karthick P.K

Posted in Configuration, Connectivity, Security | Tagged: , , , , | 6 Comments »

Behavior of SQL Server default instance on a NON-Default port

Posted by Karthick P.K on January 15, 2009

Unable to connect to SQLServer listening on Non-Default Port?

If you connect to SQLServer using the “hostname” it connects to the instance which is listening on 1433.

If you make named instance listen on 1433 and connect using “hostname” it connects to named instance which is listening on 1433.

if you connect to SQLServer using hostname or ip-address (without “\instance name”) it connects to instance which is listening on 1433, it can be named instance as well.(We have a belief that it connects to default instance because default port of default instance is 1433).

When you are connecting to SQLServer using hostname (without “\instance name”) client will not communicate with SQL Browser.
SQLClient would communicate with SQLBrowser only if there is “\instancename”  followed by host name.

Dynamic port detection is only available for named instances of SQL Server 2000.
The behavior of SQL Server 2000 for a default instance is exactly the same as in earlier versions of SQL Server. The network libraries assume either 1433 or the
global default port established with the Client Configuration Utility.

If a default instance is listening on a port other than the standard 1433 port, you can provide an alias or change the global default port. You can also connect to the
instance of SQL Server by using its server name, its FQDN, or its IP address followed by a comma and the port number.

The mdac rules :

If you connect to a Named Instance, then you need to either specify the Server\Instance or Server,Port.
If you change the default port number for the default instance, then you need to specify Port number as well.

Regards

Karthick P.K

Posted in Configuration, Connectivity | Tagged: , , , | 16 Comments »

Unable to start SQLServer agent resource on cluster after upgrading to 9.00.3186 or Higher

Posted by Karthick P.K on January 14, 2009

SQLServer agent resource fails to come online on cluster after upgrading to build 9.00.3186 or Higher

 

Error

2008-03-19 23:06:23 – ? [100] Microsoft SQLServerAgent version 9.00.3186.00 (x86
unicode retail build) : Process ID 2428
2008-03-19 23:06:23 – ? [101] SQL Server SNETNAME version 9.00.3186 (0 connection
limit)
2008-03-19 23:06:23 – ? [102] SQL Server ODBC driver version 9.00.3042
2008-03-19 23:06:23 – ? [103] NetLib being used by driver is DBNETLIB.DLL; Local
host server is
2008-03-19 23:06:23 – ? [310] 4 processor(s) and 2560 MB RAM detected
2008-03-19 23:06:23 – ? [339] Local computer is SNETNAME running Windows NT 5.2
(3790) Service Pack 2
2008-03-19 23:06:23 – ? [432] There are 11 subsystems in the subsystems cache
2008-03-19 23:06:38 – ! [364] The Messenger service has not been started – NetSend
notifications will not be sent
2008-03-19 23:06:38 – ? [129] SQLSERVERAGENT starting under Windows NT service
control
2008-03-19 23:06:38 – + [260] Unable to start mail session (reason: No mail profile
defined)
2008-03-19 23:06:38 – + [396] An idle CPU condition has not been defined – OnIdle
job schedules will have no effect
2008-03-19 23:06:38 – + [408] SQL Server MSSQLSERVER is clustered – AutoRestart has
been disabled
2008-03-19 23:06:39 – ! [298] SQLServer Error: 22022, CryptUnprotectData() returned
error -2146893813, ‘Key not valid for use in specified state.’ [SQLSTATE 42000]
2008-03-19 23:06:39 – ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
2008-03-19 23:06:40 – ? [098] SQLServerAgent terminated (normally)
Error2

2008-03-18 12:18:30 – ? [100] Microsoft SQLServerAgent version 9.00.3200.00
((Unknown) unicode retail build) : Process ID 6512
2008-03-18 12:18:30 – ? [101] SQL Server PISTONDIST version 9.00.3200 (0 connection
limit)
2008-03-18 12:18:30 – ? [102] SQL Server ODBC driver version 9.00.3042
2008-03-18 12:18:30 – ? [103] NetLib being used by driver is DBNETLIB.DLL; Local
host server is np:pistondist
2008-03-18 12:18:30 – ? [310] 16 processor(s) and 32765 MB RAM detected
2008-03-18 12:18:30 – ? [339] Local computer is PISTONDIST running Windows NT 5.2
(3790) Service Pack 2
2008-03-18 12:18:31 – ? [432] There are 11 subsystems in the subsystems cache
2008-03-18 12:18:31 – ! [364] The Messenger service has not been started – NetSend
notifications will not be sent
2008-03-18 12:18:31 – ? [129] SQLSERVERAGENT starting under Windows NT service
control
2008-03-18 12:18:32 – + [396] An idle CPU condition has not been defined – OnIdle
job schedules will have no effect
2008-03-18 12:18:32 – + [408] SQL Server MSSQLSERVER is clustered – AutoRestart has
been disabled
2008-03-18 12:18:32 – + [162] Internal request (from SetJobNextRunDate [reason:
schedule will not run again]) to deactivate schedule 66
2008-03-18 12:18:32 – ! [298] SQLServer Error: 22022, CryptUnprotectData() returned
error -2146892987, ‘The requested operation cannot be completed. The computer must
be trusted for delegation and the current user account must be configured to allow
delegation.’ [SQLSTATE 42000]
2008-03-18 12:18:32 – ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
2008-03-18 12:18:33 – ? [098] SQLServerAgent terminated (normally)

 

Resolution

Modify the the following Key.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\SQLServerAgent
Modify the value data of the serverhost key to np:Virtualservername

Ie:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.X\SQLServerAgent
ServerHost
Value: np:Virtualservername.

This will force the SQLServer agent to connect with SQLserver using Named Pipes so
delegation is not used.

—————————————————————————————————————–

We have a HOTFIX available for this issue and it is included in the cumulative update pack9 for SQLServer service pack2. http://support.microsoft.com/?id=956378
Note: Before applying the Hotfix. you have to follow the  steps mentioned in Resolution else hotfix would fail. Revert the steps after applying the fix.

 

Regards

Karthick PK

Posted in Configuration, Connectivity, SQL Cluster Setup, SQL Server Cluster | Tagged: , , , , , | 2 Comments »