PREEMPTIVE_OS_AUTHORIZATIONOPS waits in SQL Server

SQL Server threads which are controlled by SOS (SQL Server operating system) are Non preemptive but at times they switch preemptive when they can’t obey the rules of SOS. Some common places when SOS thread is switched preemptive are when we call extended proc’s, few Windows API etc.

 

Let us assume you use “execute as user x” in your job, SQL Server calls Windows functions like LookupAccountName  to get the credential of user. Windows functions interacts with AD services to get the credentials of account and return the info to the caller in SQL Server process and then SQL Server would build the logintoken. If there is a delay in AD and if it takes long time to respond to the windows function calls other threads in the same scheduler would get blocked so SQL Server thread would switch preemptive (Doesn’t follow SOS rules) before  making these  function  calls. PREEMPTIVE_OS_AUTHORIZATIONOPS wait type would occur when a thread is waiting on such windows functions (security) to return, So first thing which would have to do is to fix the performance of AD calls.

 

To narrow down and prove that this issue occurs because of Active directory performance. Login to SQL server using the startup account of SQL Server and execute below query when you notice PREEMPTIVE_OS_AUTHORIZATIONOPS wait type and compare the times printed. It will give you the time it takes for SQL Server to complete the AD calls.

 

 

create procedure PREEMPTIVEOSAUTHORIZATIONOPS  with execute as self

as

set nocount on

select CONVERT(varchar, getdate(), 126) PREEMPTIVEOSAUTHORIZATIONOPS

go

print convert(varchar, getdate(), 126)

exec dbo.PREEMPTIVEOSAUTHORIZATIONOPS;    

print convert(varchar, getdate(), 126)

go

Advertisements

A SQL product other than SQL Server 2014 CTP1 is detected. You cannot install this release until the existing instances of SQL products are uninstalled

When you install SQL Server 2014 set support rules would fail in “SQL product installation rule” with below error

—————————
Rule Check Result
—————————
Rule “Previous SQL product installation” failed. Â
A SQL product other than SQL Server 2014 CTP1 is detected. You cannot install this release until the existing instances of SQL products are uninstalled.

—————————
OKÂ Â Â
——————–

image

Cause

SQL Server 2014 CTP 1 is prerelease software and should be installed only on a clean machine with no other SQL Server instance.

More details in http://msdn.microsoft.com/en-us/library/dn169381(v=sql.15).aspx

Extract from above link

{

1.1 Limitations and Restrictions in SQL Server CTP 1

Issue: The following sections define the limitations and restrictions that apply to SQL Server CTP 1.

1.1.1 General limitations and restrictions
  • SQL Server 2014 CTP 1 is prerelease software and should be installed only on a clean machine.

  • SQL Server 2014 CTP 1 is a NON-PRODUCTION release and should not be installed and used in production environments.

  • SQL Server 2014 CTP 1 may contain branding from Microsoft SQL Server 2012 and is versioned as 11.0.

  • SQL Server 2014 CTP 1 is only available in the English language.

  • Microsoft SQL Server 2014 CTP 1 is not supported by Microsoft Customer Services and Support (CSS).

}

SQL Server Error while enabling Windows feature : NetFx3, Error Code : -2146498298

SQL Server 2012 setup might fails with below error when you do not have .Net frame work 3.5 features.

{
TITLE: Microsoft SQL Server 2012 Setup
——————————
The following error has occurred:
Error while enabling Windows feature : NetFx3, Error Code : -2146498298 , Please try enabling Windows feature : NetFx3 from Windows management tools and then run setup again. For more information on how to enable Windows features , see
http://go.microsoft.com/fwlink/?linkid=227143
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.2100.60&EvtType=0x681D636F%25401428%25401
——————————
BUTTONS:
}

 

To resolve the above error open server manager à Add roles and features wizard à select .Net frame work 3.5 feature and install as shown in below image.

clip_image002[9]

If the installation of.Net frame work 3.5 feature fails most probably it should be because of incorrect SXS folder. In such case choose an alternate SXS folder for installation in “confirm installation screen” (Which would appear as soon as you click install on above screen)
You will find SXS folder in Windows DVD under \sources\SXS

windows cluster freezes at “waiting for notification that node ‘‘ is a fully functional member of the cluster”

Adding node in windows cluster freezes at “waiting for notification that node ‘‘ is a fully functional member of the cluster”

When you try to add a node to cluster in windows2008 or windows2008 R2 or windows 2012 it may get stuck in the below screen With message “waiting for notification that node is a fully functional member of the cluster

After some time you will notice “operation is taking longer time than expected” and eventually after the time out add node wizard will fail with error “Unable to successfully cleanup”.

clip_image001

You may notice “the operation is taking longer than expected in some time”

If you look at cluster diagnostic log you may find

[RHS] RhsCall::Perform_NativeEH: ERROR_NOT_READY(21)’ because of ‘Startup routine for ResType MSMQTriggers returned 21.’

Failed to write SPNs to node’s computer object – status 5

Resolution

1. To resolve the exclude UDP port 3343 from fire wall.

2. If the issue persists after opening the UDP port 3343. Check failover clustering diagnostic event for error “Failed to write SPNs to node’s computer object – status 5”. If you find above error make sure http://support.microsoft.com/kb/976424 is applied in Domain controller.

3. If you find event ID 1289 “The Cluster Service was unable to access network adapter ‘Microsoft Failover Cluster Virtual Miniport’. Verify that other network adapters are functioning properly and check the device manager for errors associated with adapter ‘Microsoft Failover Cluster Virtual Miniport’. If the configuration for adapter ‘Microsoft Failover Cluster Virtual Miniport’ has been changed, it may become necessary to reinstall the failover clustering feature on this computer” follow the steps in http://support.microsoft.com/kb/973838

4. Make sure IPv6 is enabled on all NIC’s

5. Enabled Cluster communication and client access on the Public network http://technet.microsoft.com/en-us/library/cc728293%28v=ws.10%29.aspx

6. Make sure Symantec Endpoint Protection is uninstalled/disabled when you add the node.

7. Make sure there is no duplicate name computer objects for nodes in AD .follow http://blogs.technet.com/b/askcore/archive/2011/05/31/cluster-installation-time-out-issues.aspx

 

Bulk insert fails with error 4861 Cannot bulk load because the file could not be opened

When you do bulk insert in SQL Server it may fail with below error because of double hop .

Error :

Msg 4861, Level 16, State 1, Line 3

Cannot bulk load because the file “\\path\” could not be opened. Operating system error code 5(failed to retrieve text for this error. Reason: 15105).

Steps to fix the above error

1. Connect to SQL Server using SSMS (With account you run bulk insert) and execute below query and check if it is using Kerberos authentication

select net_transport,auth_scheme from sys.dm_exec_connections where session_id=@@spid

2. If the session is not using Kerberos authentication then fix the SPN issues (startup account of SQL Server should have read and write SPN permissions). Account which is running bulk inser should have read SPN permission. Setpn exe or adsiedit can be used to add or display all the SPN’s. If the SPn’s are registered properly and still connection fails to NTLM then get the output of SSPIClient.exe and verify why Kerberos authentication fails.

3. Make sure the account which is running bulk insert is trusted for delegation in Active Directory.

4. Account which is running bulk insert should have access to the shared directory in which BCP files are placed.

Reference  BULK INSERT (Transact-SQL) 

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

Security Account Delegation (Impersonation)

If a SQL Server user is logged in using Windows Authentication, the user can read only the files accessible to the user account, independent of the security profile of the SQL Server process.

When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.

To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.

For more information about this and other security considerations for using BULK INSERT, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK…) (SQL Server).

CryptAcquireContext and CryptReleaseContext example

CryptAcquireContext and CryptReleaseContext example

#include <windows.h> 
#include <string> 
#include <winbase.h> 
#include <iostream> 
using namespace std;
#include <Wincrypt.h >
  
                                      
void main()
{
LPCSTR rgwchKeyContName = "Test123456";  
HCRYPTPROV m_hCryptoProviderFB;
BOOL ret;
BOOL ret2;

ret=CryptAcquireContext(&m_hCryptoProviderFB, rgwchKeyContName, MS_ENHANCED_PROV, PROV_RSA_FULL, CRYPT_SILENT);
	
if (!ret && GetLastError() == NTE_BAD_KEYSET)

{
	
	printf("\nUnable to open Keyset.CryptAcquireContext failed with error: 0x%X . \nWe will try creating key",GetLastError());

	ret2=CryptAcquireContext(&m_hCryptoProviderFB, rgwchKeyContName, MS_ENHANCED_PROV, PROV_RSA_FULL, CRYPT_NEWKEYSET | CRYPT_SILENT);
		if (!ret2)
		{
		printf("\nCryptAcquireContext failed creating key.Error: 0x%X",GetLastError());
		}
		else
		{
		printf("\nKey created");
		}
	exit;
}


else if (!ret && GetLastError() == NTE_BAD_KEYSET)
{
printf("CryptAcquireContext failed with error: 0x%X",GetLastError());
}

else
{

	printf("CryptAcquireContext opened key. Return value is 0x%X.",ret);
}


	if (CryptReleaseContext(m_hCryptoProviderFB,0))
	{
	//printf("\nHandle has been released.\n");
	}
	else
	{
	printf("\nHandle could not be released.\n");
	}

}

Criticalsection example

Criticalsection and CreateThread example (EnterCriticalSection LeaveCriticalSection)

#include <windows.h> 
#include <string> 
#include <iostream> 
#include <process.h>    /* _beginthread, _endthread */
long a=0;
long b=0;
int Threadcount=64;
int s=Threadcount;
CRITICAL_SECTION  gcs; 
void Submain(void *x)
{
	for (int L=0;L<1000;L++) 
		{

			a=a++;
			EnterCriticalSection(&gcs);
						b=b++;
			LeaveCriticalSection(&gcs);
		}

/*
    s=s-1;  //Simple synchronization technique. May be useful if you like to increase the thread count WaitForMultipleObjects support value defined for MAXIMUM_WAIT_OBJECTS 64
	if(s==0)
	{
		d=TRUE;
	}
*/
_endthread();
}

 
void main()

{

HANDLE *hThreads;
hThreads = new HANDLE[Threadcount] ;
InitializeCriticalSection(&gcs);
for (int i=0;i<Threadcount;i++)
{
hThreads[i]=	CreateThread(NULL,NULL,(LPTHREAD_START_ROUTINE  )Submain,  NULL,  0,  NULL);
 	
		if (hThreads[i]==NULL)
		{
			printf("\nThread creation failed for thread %d with error %d",i,GetLastError());
		}

}

DWORD rw=WaitForMultipleObjects(Threadcount,hThreads,true,INFINITE);
DeleteCriticalSection(&gcs);
//while(!d); //Simple synchronization technique 

printf("Value of a is:%d\n" ,a);
printf("Value of b is:%d\n" ,b);
system("pause");
}