Microsoft SQL 2005 Bulk Insert – Kerberos double hop – and Operating system error code 5 (Access is denied)

Description

Microsoft SQL 2005 Bulk Insert – Kerberos double hop – and Operating system error code 5 (Access is denied)
After upgrading Microsoft SQL Server 2000 SP4 to Microsoft SQL 2005 SP2, a stored procedure with
a bulk insert statement, started to throw an error.

Error

Cannot bulk load because the file "\\ServerA\NetworkShare\Import\20080315.csv" could not be opened. Operating system error code 5(Access is denied.).

TSQL

The t-sql code that caused the problem:
declare @ImportFile as varchar(255)
set @ImportFile = ‘\\ServerA\NetworkShare\Import\20080315.csv
exec (‘bulk insert dbo.ImportTable from ”’ + @ImportFile + ”’ WITH (FIELDTERMINATOR = ”,”, ROWTERMINATOR = ”\n”)’)

Infrastructure

To understand the error, you must first understand the environment infrastructure.

ServerA

Microsoft Windows Server 2003 Standard SP1

Server is member of an OU with the High Secure Template C:\WINDOWS\security\templates\ hisecws.inf applied to it.

Server contains a custom made .NET 2.0 WindowsService with Identity DOMAIN\srvaWindowService

.NET 2.0 WindowsService has a database connectionstring: "Data Source=ServerB;Initial Catalog=DBTEST;Integrated Security=True; "

Server contains a Network Share ‘\\ServerA\NetworkShare\’

 

ServerB

Microsoft Windows Server 2003 Standard SP1

Microsoft SQL Server 2005 SP2

Microsoft SQL Server 2005 named instance (ServerB\InstanceName) on static port 1433

Microsoft SQL Server 2005: Only Shared Memory en TCP/IP enabled (Named Pipes are Disabled)

Microsoft SQL Server 2005 Server authentication: Windows Authentication mode

The Microsoft SQL Server 2005 named instance identity is a Active Directory account DOMAIN\srvaSQLServer

Microsoft SQL Server 2005 instance (ServerB\InstanceName) contains a database (DBTEST) with Compatibility level: SQL Server 2005 (90)

 

Active Directory Server

Microsoft Windows Server 2003 Standard SP1

Server is member of an OU with the High Secure Template C:\WINDOWS\security\templates\hisecdc.inf applied to it.

 

Accounts and Rights

Active Directory account DOMAIN\srvaSQLServer is local admin on ServerB (causes a problem, see solution)

Active Directory account DOMAIN\srvaSQLServer has no Service Principal Name for the MS SQL Service on port 1433(causes a problem, see solution)

Active Directory account DOMAIN\srvaSQLServer is not "Trusted for Delegation" (causes a problem, see solution)

Active Directory account DOMAIN\srvaSQLServer has "log on as a service" rights

The Active Directory account DOMAIN\srvaWindowService has modify rights to the networkshare ‘\\ServerA\NetworkShare\’

The Active Directory account DOMAIN\srvaWindowService has modify rights to the filesystem networkshare ‘\\ServerA\NetworkShare\’

Active Directory account DOMAIN\srvaWindowService has "log on as a service" rights.

Active Directory account DOMAIN\srvaWindowService is mapped to a login (DOMAIN\srvaWindowService) on the Microsft SQL Server 2005 instance (ServerB\InstanceName)

The Microsoft SQL Server 2005 Login DOMAIN\srvaWindowService is member of the server role "bulk admin"

The Microsoft SQL Server 2005 Login DOMAIN\srvaWindowService is mapped to a DBTEST database user DOMAIN\srvaWindowService

The database user DOMAIN\srvaWindowService has db_datareader and db_datawriter rights on the database DBTEST.

ServerB is not trusted for delegation (causes a problem, see solution)

 

Problem Cause

A file on a network share on ServerA is processed by a .NET 2.0 Windows Service on Server A with Identity (DOMAIN\srvaWindowsService). During processing a stored procedure on Server B is called with parameter ‘\\ServerA\NetworkShare\Import\20080315.csv‘ with Windows Integrated Security. The stored procedure tries to bulk inserts the data in the database. In SQL Server 2000 this processing will succeed, because SQL Server 2000 will automaticly delegate the call to the network share on behave of the Active Directory account (DOMAIN\srvaWindowsService), so the call succeeds. Microsoft considered this a security hole and Microsoft SQL Server 2005 will not automaticly delegate control.

Rich Baumet, explains on a MSDN forum
If you were to execute the statement on the SQL Server itself then it will work. If you were to move the file locally to the SQL Server this will work. Both will work because you are no longer doing a double hop to execute and get the file.

 

Solution

After following the steps on the MSDN Forum http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928173&SiteID=1 it still did not work.
This problem was, that after restarting the Microsoft SQL Server 2005 instance service, the SPN disappeared from active directory. After some testing we found the solution that worked for us.
Add the Active Directory account DOMAIN\srvaSQLServer to the domain admin group.
Restart the Microsoft SQL Server 2005 instance service.

After restarting the service you should see a new Service Principal Name in Active directory:
In the Microsft Windows 2003 support tools, you can found the executable setspn.exe
Execute setspn -L DOMAIN\srvaSQLServer

Registered ServicePrincipalNames for CN=srvaSQLServer,OU=Users,DC=domain,DC=corp:
MSSQLSvc/ServerB.domain.corp:1433

Follow the step 4 on the MSDN Forum
(Now that we created the SPN for the SQL Service we must allow it to delegate. Using Active Directory Users and Computers go to the properties of the account the SQL Service is running under.There should be a DELEGATION tab there On the Delegation tab select "Trust this user for delegation to any service (Kerberos only)

This step must be repeated for ServerB (go to active directory, select server ServerB, go to the tab delegation and select "Trust this user for delegation to any service (Kerberos only)

After following this steps it still did not work!!!
This was caused by the caching of the Keberos tickets. During the bulk insert error the ServerA en ServerB, got a Keberos tickets that denied access to the network share. These tickets are cached. Clear the caching (purchase tickets) with the kerbtray.exe tool found in de the Microsft Windows 2003 Resource Kit

 

Additional Information

MSDN Forum

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928173&SiteID=1

This post started the solution for us, many tanks to Rich Baumet

MSDN Forum

http://forums.microsoft.com/msdn/showpost.aspx?postid=107193&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

This form ends with: After watching forums, etc. for almost a year regarding this issue, we ran a MS Support Request. After several hours of walking them through the issue, they said it was definitely a bug ( # 396489 ) in 2005 and would be fixed in 2008.
In our situation it was not a bug in Microsoft SQL Server 2005 SP2, but a configuration error in Active Directory

 

Microsoft SQL Server, Error: 233, No process is on the other end of the pipe

Problem
When connecting with windows authentication, the following exception was thrown:

A connection was successfully established with the server, but then an error occurred during the login process.
(provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)
(Microsoft SQL Server, Error: 233)

Solution
Turns out the user had a default database that no longer existed.
Changing the default database to Master solved the problem

Microsoft SQL Server Agent Create Job Error

Microsoft SQL Server Agent Create Job Error
When you try to create a Microsoft SQL Server Agent Job in Microsoft SQL Server 2005 Management Studio, you get the error:

Unable to cast object of type ‘Microsoft.SqlServer.Management.Smo.SimpleObjectKey’ to type ‘Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey’. (Microsoft.SqlServer.Smo)

Installing Microsoft SQL Server 2005 SP2 solved the problem

Performance Microsoft SQL Server 2005 Management Studio on Remote Desktop

When Microsft SQL Server 2005 Management Studio is very slow on startup, disable Internet Explorer setting [Check for publisher’s certificate revocation]
Open [Microsoft Internet Explorer]
Click on [Tools]
Click on [Internet Options]
Click on Tab [Advanced]
Disable [Check for publisher’s certificate revocation]
Click on [OK] and close [Microsoft Internet Explorer]

An error has occurred during report processing. Login failed for user '(null)'

The error An error has occurred during report processing. Login failed for user ‘(null)’ can occur, when "anonymous access" is enabled on the Microsoft Reporting Service virtual directory and the datasource in Microsoft Reporting Services uses Windows Integrated Security

Solution
Disable "anonymous access" on the datasource in the microsoft sql server reporting manager

Named Pipes before TCP/IP causes "SQL Server does not exist or access denied" error message

Applies to Microsoft SQL Server 2000
If Named Pipes come before TCP/IP in the SQl Server Client Network Utility, you can get the error message "SQL Server does not exist or access denied", when you try to connect remotely to your Microsoft SQL Server 2000 instance. When you connect localy on the server everything seems to work fine.

Solution
Disable all network protocols except TCP/IP in the SQl Server Client Network Utility
Restart the service of your Micorosft SQL Server 2000 instance.

Use the correct version of InstallUtil.exe

When you want to use "InstallUtil.exe" for installing a windows service, make sure that you use the correct version. You can only uninstall a .NET 2.0 windows service with the "InstallUtil.exe" in the folder "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\InstallUtil.exe"
like:
"C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\InstallUtil.exe" /u "C:\Program Files\ADA ICT\WindowsService1.exe"

Drop all non clustered indexes in Microsoft SQL Server database

if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N‘[tempdb].[dbo].[##NonClusteredIndexesToDelete]’))
        drop table ##NonClusteredIndexesToDelete

create Table ##NonClusteredIndexesToDelete
    ([Name] sysname, [TableName] sysname)

insert ##NonClusteredIndexesToDelete
select
    sysindexes.[name],
    sysobjects.[name] as TableName
from
    sysindexes (nolock)
inner
join
    sysobjects on sysindexes.id = sysobjects.id
where    (indid > 1)                                        — delete only non clustered indexes
and        (sysindexes.[name] not like ‘_WA%’)                    — don’t delete statistics
and        (sysobjects.[type] != ‘S’)                            — don’t delete system indexes
and        (sysobjects.[xtype] != ‘IT’)                        — don’t delete system indexes
and        (not(sysindexes.[status] = 4098 and sysindexes.indid = 2))        — don’t delete unique indexes
order by (sysindexes.[name])

declare @name sysname
declare
@TableName sysname
declare
@sql varchar(4096)
declare vCursor cursor for
        select [name], [TableName] from ##NonClusteredIndexesToDelete order by [name]

open vCursor
fetch NEXT from vCursor into @name, @TableName
while @@FETCH_STATUS = 0
    begin
        set @sql = ‘drop index ‘ + @TableName + ‘.’ + @name
        exec (@sql)
        fetch NEXT from vCursor into @name, @TableName
    End    

close vCursor
deallocate vCursor

drop table ##NonClusteredIndexesToDelete

Select all indices from a database in Microsoft SQL Server 2005

Select all indices from a database in Microsoft SQL Server 2005

select
    [name]
from
    sysindexes (nolock)
where ([name] not like ‘PK%’)
    and ([name] not like ‘_WA%’)
    and ([name] not like ‘sys%’)
    and ([name] not like ‘ncsys%’)
    and ([name] not like ‘tsys%’)
    and ([name] not like ‘ucsys%’)
    and ([name] not like ‘nc1sys%’)
    and ([name] not like ‘nc2sys%’)
    and ([name] not like ‘tdtproperties’)
order by [name]

Asynchronous webservice calls without blocking/delaying rendering in ASP .NET 2.0 C#

When you use the "Async" methods of a WebService in .NET 2.0 the ASP .NET 2.0 page will delay rendering until completion of the Async method call.
This can be very handy, but what if you do not want to delay the rendering of the page, because you are not interested in the result.
Then you must use another method: Delegates, BeginInvoke and Synchronous WebService call
Yes, this is right "Synchronous WebService call"

    /// The delegate, used for asynchronous processing.
    public delegate TOutput SomeMethod<TOutput>();

    /// <summary>
    /// Click Event, user clicked on the LinkButtonTest.
    ///
    /// History
    /// [RVL 01-feb-2008] Creation
    /// </summary>
    protected void LinkButtonTest_Click(object sender, EventArgs e)
    {
     this.AsnchronousProgramming();
    }

    /// <summary>
    /// Test the asynchronous execution of a method via a delegate and BeginInvoke in .NET 2.0
    ///
    /// History
    /// [RVL 01-feb-2008] Creation
    /// </summary>
    private void AsnchronousProgramming()
    {
     Debug.WriteLine("Start the asynchronous processen");
     SomeMethod<int> method = this.SomeCalculation;
    
     method.BeginInvoke(EndSomeCalculation, method);

     Debug.WriteLine("We can do some processing here");
    }

    /// <summary>
    /// The calculation is done.
    ///
    /// History
    /// [RVL 01-feb-2008] Creation
    /// </summary>
    public void EndSomeCalculation(IAsyncResult result)
    {
     Debug.WriteLine("SomeCalculatoin completed");
     SomeMethod<int> method = result.AsyncState as SomeMethod<int>;

     Debug.WriteLine("Get result");
     int i = method.EndInvoke(result);
    
     Debug.WriteLine("Asynchronous processing completed");
    }

/// <summary>
    /// Some calculation to demonstrate the asynchronous execution.
    ///
    /// History
    /// [RVL 01-feb-2008] Creation
    /// </summary>
    private int SomeCalculation()
    {
     int result = 1;

     Debug.WriteLine("Simulate syncronous webservice call or lang during processing (10s)");
     Thread.Sleep(10000);

     Debug.WriteLine("Calcula
te the result"
);
     result += 1;

     return result;
    }

See also: Matthew Cochran, http://www.c-sharpcorner.com/UploadFile/rmcochran/multithreadedasync05112007061031AM/multithreadedasync.aspx