The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

Posts Tagged ‘access denied’

How To: SQL Server Bulk Insert with Constrained Delegation (Access is Denied)

Posted by Sudarshan Narasimhan on December 30, 2011

Well folks, I’ve not been able to blog for sometime as I was caught up with work. But fear not, these past 2 months have given me enough subject material for multiple blog posts. Let me start off with one regarding setting up SQL Server for bulk inserts from a network file share when constrained delegation is setup in Active Directory.

For SQL Server folks out there, I don’t expect you to know about Delegation let alone Constrained vs. Unconstrained.


Q: What is Delegation?
A: A client connected to an instance of SQL Server can connect to another instance of SQL Server or another machine by forwarding the credentials of an authenticated Windows user.

E.g. Domain\User1 on Client1 connects to –> SQL Server SQL1 and accesses a remote file on –> Server2, using his own credentials i.e. Domain\User1

It is the role of the SQL Server machine SQL1 to impersonate/delegate that user when communicating with Server2. For this to happen, the SQL Server has to be configured to allow delegation (in Active Directory).

Q: What is Constrained Delegation?
A: This feature allows administrators to specify exactly which services a server or a domain account can access when using an impersonated user’s security context. More on this later. If there is no such restriction configured, then it is called as unconstrained delegation, where every service for that user can perform impersonation aka delegation. It is a security best practice to perform constrained delegation as it reduces the surface area of any attacks.


Here is a brief layout of the environment that I am going to use in this post as a reference.


This is a basic 3 machine architecture that is very common. Here are the various parties involved:-

  1. Client Computer – Windows PC/Workstation/Application Server: machine name TRINITY1, Client account Domain\appadmin
  2. SQL Server – Windows Server 2003+ running SQL Server 2005/2008/2008 R2 standalone instance: machine name NEOSQL, SQLSvc account Domain\sqladmin
  3. File Share on another Windows Server (basically a shared folder): machine name MORPHEUS1, where user Domain\appadmin has Full Control on the shared folder.



In an ideal scenario, here is how I want things to work. When a client application runs the SQL BULK INSERT command logged in as Domain\appadmin account to SQL Server, I want the same appadmin account to access the remote file share and read the data.

1. If a user uses a SQL Server login to connect to SQL, then the SQL Service account credentials are used to access the remote file share.

2. If a user uses a Windows account, then his own account is used to access the file share and for this to work successfully, delegation has to be configured.

BULK INSERT appdb.dbo.bulktbl

FROM ‘\\morpheus1\share\data.txt’

If not configured correctly then you will get this error when running the above bulk insert command.

Msg 4861, Level 16, State 1, Line 3

Cannot bulk load because the file "\\morpheus1\share\data.txt " could not be opened. Operating system error code 5(Access is denied.).

Configuring Unconstrained Delegation

1. Configuring permissions on the shared folder on Morpheus1. As you can see the appadmin windows account has Full Control and the sqladmin account has Read/Write permission (if any sql login is going to be used, this is not mandatory).


2. I am assuming that the SQL Server is running under the service account Domain\sqladmin. Login to the Domain Controller with Domain Admin rights and open up Active Directory Users and Computers MMC snap-in.

a. Make sure that the following is NOT checked.


b. The SQL Service account needs SPN’s (Service Principal Names) to be created before it can be configured for delegation. You can use the SetSPN.exe tool that is available with Windows SDK or sysinternals toolkit to create the SPN’s. This tool is also available bundled along with Windows Server 2008.

We need to create 4 SPN’s for the account running SQL Server service as shown below. (2 with netbios names and 2 with FQDN). Note: The command below is for a standalone default instance of SQL Server running on default port 1433. Please modify as applicable to your environment.

Setspn -A MSSQLSvc/neosql thematrix\sqladmin
Setspn -A MSSQLSvc/neosql:1433 thematrix\sqladmin
Setspn -A MSSQLSvc/ thematrix\sqladmin
Setspn -A MSSQLSvc/ thematrix\sqladmin

Once done you can query the SPN’s using setspn.exe and it should list you these 4 SPN’s.


c. Switch to the Delegation Tab and select the radio button by Trust this computer for delegation to any service (Kerberos only). We are setting up unconstrained delegation if this option is chosen. If you do not see the Delegation tab available, then there was some issue with creating the SPN’s in step (b) listed above.


d. Next find the actual machine account in Active Directory for the SQL Server machine NEOSQL and set the computer account to be trusted for delegation, as we did above.


e. Next, we need to check the file server where the file we want to import is located, i.e. MORPHEUS1. We need to verify that this machine has the normal 2 HOST SPN’s registered.


This is required to use Kerberos for authentication. Delegation will not work without this, and you will receive an error otherwise. By default each machine should have 2 HOST SPN’s created for it.


3. On the SQL Server machine NEOSQL, open up the Local Security Policy by going to secpol.msc –> Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment, and add the sql account Domain\sqladmin to the following policies:

  • Act as part of the operating system
  • Impersonate a client after authentication

So far the configuration given above is for unconstrained delegation. We need to make sure that this setup works before we can configure constrained delegation. Once you are done till there, reboot the SQL Server machine (NEOSQL) and the file share machine (Morpheus1), so that all the changes we made in Active Directory are reflected when the machine starts up.


Make a test connection from the client machine (TRINITY1) using sqlcmd or SSMS. Open up another connection from SSMS and run the following query to find out if the connection from the client machine is using Kerberos authentication.

select b.spid, b.hostname, b.program_name, a.auth_scheme

from sys.dm_exec_connections a

inner join sys.sysprocesses b

on a.session_id = b.spid


The connection from the client machine should return KERBEROS. This is required for delegation to work. If it did, then you are good to run the bulk insert statement and it should work.

Configuring Constrained Delegation

So far so good. Now comes the part that is the crux of this blog post. If you were successful in getting things to work, then read on….

1. Open up Active Directory Users and Computers MMC snap-in with a Domain Admin account as before. Open up the Domain\sqladmin account and switch to the Delegation Tab.

2. Choose the option “Trust this computer for delegation to specified services only”, select “Use any authentication protocol” and click on the “Add…” button.


In the dialog to select the sqladmin account (or your account which runs the SQL service).



This should list the 2 SPN’s we have previously created for this account. Select both of them and click OK.


Click on the “Add…” button again and enter the SQL Server machine name (NEOSQL) and choose the HOST service.


Click on the “Add…” button again and enter the File Share machine name (MORPHEUS1) and choose the HOST Service. We are actually interested only in the CIFS and Protected Storage service. Choosing HOST will automatically choose these 2 services.




After adding all of these, this is how your final configuration for the sqladmin account will look like.


We need the SQLSvc account (Domain\sqladmin)and file share server to have CIFS service enabled for delegation because we are accessing a remote file share and it is the role of the CIFS Service (Common Internet File Share) to perform this. If we do not have CIFS (which comes when we added the HOST Service), then your account (Domain\appadmin) from the client machine (TRINITY1) will reach the file share server (MORPHEUS1) as NT AUTHORITY\ANONYMOUS LOGON and this will not have access and fail with 0x5 (Access is Denied).

I tested this by enabling auditing on the shared folder and saw this. This is a easy test to perform to check if delegation is working or not

When the bulk insert fails with access denied we will see this in the security event log of the file server (MORPHEUS1),

Log Name:      Security
Source:        Microsoft-Windows-Security-Auditing
Date:          12/20/2011 11:17:33 PM
Event ID:      4624
Task Category: Logon
Level:         Information
Keywords:      Audit Success
User:          N/A
An account was successfully logged on.

    Security ID:        NULL SID
    Account Name:        –
    Account Domain:        –
    Logon ID:        0x0

Logon Type:            3

New Logon:
    Security ID:        ANONYMOUS LOGON
    Account Name:        ANONYMOUS LOGON
    Account Domain:        NT AUTHORITY

That’s all folks! Keep in mind that this configuration requires a good bit of handling with Active Directory and/or policies. Please work with your Domain Administrator while making these changes. As always, stay tuned for more… Cheers and a happy new 2012 to y’all!



Posted in Security | Tagged: , , , , , , , , , | 34 Comments »

The Dabbling Developer Series: Part1-Windows Service Management

Posted by Sudarshan Narasimhan on September 30, 2011

Now and then when I get some time I like to spend it writing code and I usually like to relate this to some work/activity that helps in my main work i.e. SQL Server. I have many small single-purpose applications/utilities written over time and I realized that having them saved on my PC is of no use. Don’t be surprised if you find C/C++ code samples posted on TheSQLDude. For the time being let me be the TheCDude and Long Live Dennis Ritchie & Ken Thompson!


Service Control Manager Test

I wrote some code to test if I can open the Windows Service Control Manager (SCM) for the local machine or remote machine with the current logged-in account. This helps me validate permissions or spot other errors when performing service related activities. For those of you who don’t know what SCM is read this article.

SCM is a system component that takes care of service management, service start-up and other service related activities.

You might ask, How did I come to care about SCM all of a sudden? What the hell does this have to do with SQL Server?

Let me explain. I came about SCM when a customer of mine came up with a SQL Server Setup issue. SmileWhen SQL Server setup goes about installing the various SQL Server components, one of the things it has to do is to create the SQL Server services like SQL Engine, RS, AS, Browser service, SQL Writer service etc. Obviously if setup is not able to do this, it will fail. For someone to create/delete/modify a Windows service, they need to talk to the Service Control Manager (SCM). How do you isolate whether the problem is with SQL Setup or some other component? This is how I ended writing this code to test SCManager.

If you want to perform windows service related activities, it works like this:-

1. You need to open the SCManager, which will let you access its database which has the list of registered services

2. If this succeeds, then depending on the operation you can either Open an existing service and modify/query its properties or create a new service and register it with SCM.


The code below is simple C code written on Visual Studio 2010. This has no copyright so feel free to re-distribute.

// scmantest.cpp : Defines the entry point for the console application.



#include "stdafx.h"

#include <windows.h>

#include <tchar.h>

#include <strsafe.h>

#include <stdio.h>

#include <conio.h>


#pragma comment(lib, "advapi32.lib")

#pragma comment(lib, "Kernel32.lib")


int _tmain(int argc, _TCHAR* argv[])


 if (argc < 2)


  _tprintf(_T("Usage : %s name_of_machine_to_test"), argv[0]);

  return 1;



     //CHAR lpMachine ;

    BOOL bReturn = FALSE;

    SC_HANDLE hScm;



    // Check that we can open the SCManager on the machine

    hScm = OpenSCManager(argv[1], NULL, SC_MANAGER_ALL_ACCESS);

    if (hScm)


        bReturn = TRUE;

        printf("OpenSCManager completed successfully\n");        





        _tprintf(_T("Could not OpenSCManager on %s. GLE = 0x%08x\n"),argv[1],GetLastError());

        return 1;




 return 0;



The above code tries to Open the SCM and if that fails then it reports the Win32 error. You can Bing! this error to find out what action to take. The application scmantest takes 1 parameter which is the Machine Name. So if you PC is called, then you would run this from the command prompt like this


Here is an output for a non-existent machine and you can see that the Win32 Error reported is 0x000006ba (The RPC server is unavailable).



This can help isolate permission issues when trying to open SCM database as you need to have Administrator rights. Sometimes certain permissions to create might be missing which you will have to grant.

If you are a Windows Admin reading this then here are some command-line instructions to operate on Windows Services

1. Query the state of an existing Windows Service – sc query ServiceName

e.g. Checking state of default instance of SQL Server.

        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 4  RUNNING
                                (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x0

If you specified an invalid service name, you will get this error,
[SC] EnumQueryServicesStatus:OpenService Failed 1060:The specified service does not exist as an installed server.

2. Create a new Windows Service – sc create …

Let me take an example of creating a SQL Server service

e.g.  Creating a Default instance of SQL Server

sc create MSSQLSERVER type= own start= demand error= normal binPath= D:\MSSQL\MSSQL.1\MSSQL\Binn\sqlservr.exe obj= LocalSystem DisplayName= "SQL Server (MSSQLSERVER)"

type –> Type of service you want to create. Leave it at OWN.
start –> Start type for the service. You can have it at automatic, disabled. We choose demand which means manual start-up.
binpath –> Complete path to the sqlservr.exe
obj –> Startup account for the service.
DisplayName –> can be anything, this is what services.msc will show us.

a) There must be a space between ‘=’ and the value.
b) Double-quotes are mandatory is there is a space in the path.


For those of you who didn’t know this, the windows service entries are present in registry in HKLM\SYSTEM\CurrentControlSet\Services\. This is the same location from where services.msc reads and displays the list of services.


3. Using SUBINACL to check permissions for Windows Services:

SubInACL is a command-line tool that is available for download here and shows you security information about files, registry keys, and services.

e.g. Query permissions on the service for default SQL Server instance

subinacl /verbose /service MSSQLSERVER /display

/owner             =system
/primary group     =system
/audit ace count   =1
/aace =everyone         SYSTEM_AUDIT_ACE_TYPE-0x2
/perm. ace count   =6
/pace =system   ACCESS_ALLOWED_ACE_TYPE-0x0
        SERVICE_START-0x10                 SERVICE_STOP-0x20                  SERVICE_PAUSE_CONTINUE-0x40        SERVICE_INTERROGATE-0x80
        READ_CONTROL-0x20000               SERVICE_USER_DEFINED_CONTROL-0x0100
/pace =builtin\administrators   ACCESS_ALLOWED_ACE_TYPE-0x0

4. Granting permissions using SUBINACL

e.g. Grant Full Control to TestUser1 on the default SQL Server instance’s service.

subinacl /service MSSQLSERVER /Grant=Domain\TestUser1=F
MSSQLSERVER : new ace for anselm\administrator
MSSQLSERVER : 1 change(s)

Other useful Resources

Using SC.EXE to Develop Windows NT Services

How to create a Windows service by using Sc.exe

I hope this post was useful for both Developers as well as Administrators. Would appreciate any feedback on this post since I am thinking of posting other useful code samples in the future. As always stay tuned for more geeky info….


Posted in Code Samples | Tagged: , , , , , , , , , | Leave a Comment »

Guide to Troubleshooting SQL Server Service startup issues

Posted by Sudarshan Narasimhan on August 9, 2011

I did post on this same topic on MSDN SQLServerFAQ and I am re-posting this here for easy reference. This is a handy guide for every SQL DBA, as they would have come across this issue at one point or another in their DBA life. Read on…

This is one of those challenging situations when customers call us at times. I can understand the gravity of the situation – your boss is behind your back, your business is down, your application is down, your website cannot accept orders or your phone is ringing off the hook with calls from the helpdesk. Don’t worry; I will guide you through some basic steps & actions that you can take to make some sense amidst all this madness.

clip_image002[4] Warning:

This blog is only a basic guide and by no means covers all possible troubleshooting techniques and meant only as a starting point.


Nothing in life is to be feared. It is only to be understood. – Marie Curie


Startup Options
Have you ever called SQL CSS for a startup issue and seen the engineer use some cool trace flags or parameters to get SQL started. Well, it’s all documented (well, most of it that’s worth knowing)

Using the SQL Server Service Startup Options

The most common ones by far that I use on SQL startup issues are:-





Start as a console application, not as a service.



Tries to start the SQL service in single user mode, i.e. only a single user can connect. This single user connection can be either  a sysadmin or a regular user connection



Tries to start the SQL service in Minimal configuration mode. This implicitly puts SQL Server in single-user mode and this also means only the system databases master, model, tempdb & mssqlsystemresource are recovered and started.



Tries to start the SQL Server should be started with the specified trace flag which follows after –T. Again this is case sensitive.



Specifies the number of megabytes (MB) of memory that SQL Server leaves available for memory allocations within the SQL Server process, but outside the SQL Server buffer pool. The default value for this is 256MB.


-m”ClientApp Name”

You can limit the connections to the specified client application. For example, -m"SQLCMD" limits connections to a single connection and that connection must identify itself as the SQLCMD client program. You can use this option when you are starting SQL Server in single-user mode and an unknown client application is taking the only available connection. Cool option J


-k  123

Limits the number of checkpoint I/O requests per second to the value specified e.g. 123 MB/sec. Refer for more info.


Trace Flags
For more information some common trace flags see here Few of the common ones that come handy during startup issues are:

1.       3608 Starts SQL and recovers only the master database.

2.       4010 Allows only shared memory connections to the SQL Server. Meaning, you will only be able to connect from the server machine itself. Client connections over TCP/IP or named pipes will not happen.

3.       4606 Disables password policy check during server startup.

4.       4022 Skips launching automatic stored procedures when SQL Server starts. This is equivalent to setting "scan for startup procs" configuration option to 0.


Here are the steps to start SQL Server from command prompt:-

1.      Right-click on the SQL Server service name in services.msc



2.  Copy the path that you see here.

3.  Open a Command Prompt window and navigate to the path from step2.

E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlservr.exe -c -m -sSQL2005



clip_image008[8] Note:

If you are using Windows Server 2008 or above, you need to open an elevated command prompt by launching cmd.exe with “Run as Administrator” option.

A common mistake that people sometime make is to try and start SQL using sqlservr.exe and you get this error,


Text: “Your SQL Server installation is either corrupt or has been tampered with. Please uninstall then re-run setup to correct this problem”

Okay, you see this message and you are thinking oh no, there goes my dinner plans!
The above message can be misleading at times. You will get the above error if you try to start SQL server from command prompt for a named instance and do not specify an instance name. Without the -s switch the default instance will try to start, but using the sqlservr.exe file from another instance binn folder. You will also get this error if you specify an invalid parameter to sqlservr.exe. So, the installation is not corrupt after all (Dinner plans back on?)

Here’s how to start a named instance,
sqlservr.exe -c -s INSTNAME

Here’s how to start a default instance,
sqlservr.exe -c

Here’s how to start a named instance recovering only master database
sqlservr.exe -c -sMATRIX –T3608

clip_image008[9] Note:

Only specify the instance name and not the servername\InstanceName. E.g. If your machine name is YODA and your instance is called MATRIX, you only specify MATRIX. Also the “-s” is case sensitive. It does not matter if there is/isn’t a blank space after the “-s” parameter.

Some Common Scenarios for Startup Failures

Scenario 1

Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log.


“The service failed to respond in a timely fashion”.


“The service failed to start”.

1.      Locate the SQL Errorlog as that is the 1st place to look if a SQL service is not starting.

2.   This can be done by looking up the -e startup parameter value for the SQL Server instance in question.



3.  Verify that the files pointed to by -d (master data file location),  -e (Errorlog location) and  -l (master log file location) actually exist and have the correct file paths and that the SQL service startup account has proper permissions. Alternately you can also get this from the registry by looking at the values SQLArg0, SQLArg1 and SQLArg2 in the following locations.

SQL 2000
Default: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters
Named: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\INSTANCENAME\MSSQLServer\Parameters

SQL 2005

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\Parameters

SQL 2008/R2


4.  Once you have located the errorlog open it in notepad and look for any errors. An example would be like this,

2010-11-20 07:50:58.250 Server Error: 17113, Severity: 16, State: 1.
2010-11-20 07:50:58.250 Server Error 2(The system cannot find the file specified.) occurred while opening file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf’ to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

The above message clearly indicates that the master.mdf was not found in the location it was expecting it to be found.


Scenario 2

You can also look into the Windows Application log for any events related to SQL Server.

initerrlog: Could not open error log file ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG1\ERRORLOG’. Operating system error = 3(error not found).

The above error indicates that SQL could not find the errorlog file in the above location. What does this mean? This means that since SQL could not find the errorlog it has no place to log messages, hence it did not start.

When you are faced with this error, then if you look into the errorlog location, you will notice log files whose date/time stamp is not current. Do not make the mistake of opening these files and troubleshooting based on messages that are not for the current startup failure. This is where looking into the application log gives you the current entries.

I hope cleared up any confusion you might have had regarding this rule, the warning status and the simple logic behind the rule. As always stay tuned for more SQL tips…

Scenario 3

2011-04-16 07:52:49.32 spid5s      Error: 17204, Severity: 16, State: 1.
2011-04-16 07:52:49.32 spid5s      FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf for file number 2.  OS error: 32(error not found).

OS Error 32 translates to “The process cannot access the file because it is being used by another process.” How did I know this? Simple. You can find the text for any windows error by running this from a command prompt à net helpmsg XXX, where XXX is the error number

In the above scenario, some other process/application has a lock on master.mdf and this isn’t allowing SQL to take access, hence the service fails to start. To find out which process it is you can use either Process Explorer or Handle.exe from sysinternals. The usual culprits are Anti-Virus or Anti-Malware services. Once you identify the process, after stopping that, attempt to start SQL again. If it fails, verify from the errorlog that the error code is still 32 and has not changed. Most people miss this trick and assume the error is the same after making some changes. Don’t take it for granted, since the error might have changed but the service still doesn’t start. This indicates that the changes done have been successful.


Some Common Causes for SQL Service startup failures

1.                   Access denied on the folders for the SQL Server Service Account, specifically on the DATA folder which contains the SQL Server system databases.

2.                   Due to some other processes like anti-virus holding a lock on the SQL database files.

3.                   Insufficient permissions granted to the SQL Server Service account.

clip_image008[10] Important:

Always make SQL service account changes via the SQL Configuration manager since only this tool sets the proper permissions or ACL’s to the new service account. Services.msc does not set ACL’s.


4.                   Server side protocols for SQL not set correctly or disabled.

5.                   Critical system database files not found due to either accidental deletion of files or disk failures.

6.                   System database files having inconsistencies preventing SQL Server startup.

7.                   Password of the service account was changed but not updated on the server that has the SQL instance installed.

8.                   Startup parameters have incorrect file path locations.

9.                   The SQL Server service is disabled in Service Control Manager.

clip_image014[4] Critical:

If you are not a SQL expert and have followed the steps above but still stuck, my personal advice would be to call SQL Support rather than doing something you will regret later. Better safe, than sorry, eh?

Additional Resources

Error Messages of SQL Server 2005 Start Up Failure

An error occurred during encryption


Posted in Startup | Tagged: , , , , , , | Leave a Comment »