Blog - Title

April, 2009

  • SQL Bulk Insert - Access is Denied

    Hey all, Mark from DS again. I have found that numerous cases have been opened where Microsoft customers are upgrading from SQL 2000 to SQL 2005. After the upgrade they were attempting to run a bulk insert statement either in the Enterprise Manager or the Management Studio application and getting an “Access is denied” error message. Here is what my customer was running:

    DROP TABLE #In_File

    CREATE TABLE #In_File(In_Data varchar(2000))

    BULK INSERT #In_File FROM '\\contoso.com\DFSRoot\share\subfolder\log.txt' WITH (FIELDTERMINATOR='\0', ROWTERMINATOR='')

    This is the error message they were getting (sound familiar anyone?):

    Msg 4861, Level 16, State 1, Line 3

    Cannot bulk load because the file "\\contoso.com\ DFSRoot\share\subfolder\log.txt " could not be opened. Operating system error code 5(Access is denied.).

    This customer had used the same command in SQL 2000 without issues. Only after the upgrade to SQL 2005 did it start failing. Per the SQL 2005 Online Books this behavior has changed in SQL 2005:

    image

    This customer had SQL running on a two node cluster (active\passive) and was attempting to import the file that happened to be on another 2 node file cluster (active\passive). So here is how to set this up properly after upgrading to SQL 2005. I will start off with a cluster configuration but I will make notes regarding a single server setup as well.

    First I want to identify all the names that I will be using. With a cluster you have a SQL Virtual network resource that I will call SQL1; there are also cluster nodes called Node1 and Node2. The SQL service is set to use a domain user account called SQL-Service-Acct. The domain name is contoso.com.

    1) The Virtual Name (SQL1) needs to be set for Enable for Kerberos Authentication. To do this open the Cluster Administrator MMC and locate the SQL1 network name resource, right click it and select Take Offline. You have to take the resource offline to be able to enable the Kerberos authentication. Once it is offline right click the resource again and select Properties. On the Parameters tab check the box for Enable Kerberos Authentication then click Ok. Right click the resource and select Bring Online. This will create a computer account in Active Directory so make sure the account that you use to perform this action above also has the user right to Add workstations to the domain. The computer account will be created in the “Computers” container. We will need to configure Kerberos delegation on the SQL service account so proceed to the next step as this depends on what the domain functional mode is set to in your environment.

    Note: If you are not running SQL on a cluster you can skip step #1 of course.

    2) Before going any further we need to talk about configuring Kerberos delegation on either the SQL service account or any computer accounts. If the domain is running in Windows 2000 (mixed or native mode) Domain Functional mode you will see differences on how to set the delegation vs. in a Windows 2003 Domain Functional mode. If the domain is in 2000 mode then on a computer account you will need to go to the properties of the computer account and simply check the box for the Trust computer for delegation on the General tab. If the domain is in 2003 mode then you will see a Delegation tab on the computer properties. In this case you need to select the radio button by Trust this computer for delegation to any service (Kerberos only).

    3) Now we will talk about the SQL service account, SQL-Service-Acct. It needs SPN’s (Service Principal Name) registered on the account and also needs to be set for delegation. If the domain functional mode is set to Windows 2000 then find the account in Active Directory Users and Computer MMC, right click select Properties and go to the Account tab. In the Account options scroll down and check the box for Account is trusted for delegation. If you do not see the Delegation tab that means that you have not added the SPN on the account. This must be done first before you will see the delegation tab. Make sure the box for Account is sensitive and cannot be delegated is NOT checked which is found on the Account tab under account options. The GUI will allow you to check both. If your domain is running in 2003 mode then you will have to go to the Delegation tab and make the same change as a computer account to enable the delegation but you first have to register the SPN’s on the SQL service account before the delegation tab will be available! You will need to go ahead and go to the next step then come back to enable the delegation.

    4) To register the SPN’s and the SPN’s to register on the SQL service account you will need to use the utility called setspn.exe. It can be downloaded here or by installing the Resource Kit tools. The SPN’s needed to be registered are as follows:

    Setspn –A MSSQLSvc/NetbiosNameOfSQLVirtualName SQLServiceAccountName

    Setspn –A MSSQLSvc/NetbiosNameOfSQLVirtualName:1433 SQLServiceAccountName

    Setspn –A MSSQLSvc/FullDNSNameofSQLVirtualName SQLServiceAccountName

    Setspn –A MSSQLSvc/ FullDNSNameofSQLVirtualName:1433 SQLServiceAccountName

    NOTE: The SPN’s that have the trailing port number are absolutely required. The SPN’s without the port number are optional. Also please note that if the instance is running on a different port number than the default 1433 then simply change the port number in the syntax.

    So in my case my SQL Virtual network name is SQL1 and the service account is SQL-Service-Acct so here is what I would run:

    Setspn –A MSSQLSvc/SQL1. contoso.com SQL-Service-Acct

    Setspn –A MSSQLSvc/SQL1. contoso.com:1433 SQL-Service-Acct

    Setspn –A MSSQLSvc/SQL1 SQL-Service-Acct

    Setspn –A MSSQLSvc/SQL1:1433 SQL-Service-Acct

    To verify that you have entered them correctly you can run the following command to list out what SPN’s are registered on the account:

    Setspn –L SQL-Service-Acct

    If you are not using the default port that SQL uses of 1433 then you need to change the syntax to reflect the port that you are using when adding the SPN’s.

    Note: If you are not running a cluster then you need to add the SPN’s using the server name that SQL is running on instead of the SQL Virtual name. Example:

    Setspn –A MSSQLSvc/SQLServer.contoso.com CONTOSO\SQL-Service-Acct

    5) Okay, moving right along now we should have the SQL Virtual name enabled for Kerberos and trusted for delegation, the SPN’s registered on the SQL service account and it is set for delegation. The next thing we need to do is make sure the SQL service account has a couple of userrights in the local security policy. Open gpedit.msc from the Start – Run and drill down to the following:

    Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment.

    Add the SQL-Service-Acct to the following policies:

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

    No need to run gpupdate as we are going to reboot here in a minute. Remember to modify the user rights assignments on both of the cluster nodes!

    6) Next find the actual machine accounts in Active Directory for the cluster nodes, node1 and node2. Set the computer accounts to be trusted for delegation as we did in step #2 for the SQL Virtual name. Once that is done reboot one of the nodes then once it is back up reboot the other node.

    Note: When not running a cluster simply set the delegation on the machine account that is running the SQL service and reboot the server.

    7) We are almost done now. We need to check the file server where the file is located that we want to import. If it is on another cluster make sure that cluster virtual resource name is enabled for Kerberos authentication as we did in step #1. If we are on a non-clustered file server then make sure we have the normal 2 HOST SPN’s registered. You can run the following command to check this:

    Setspn –L fileserver.contoso.com

    You should receive the two default HOST SPN’s back such as these:

    HOST/fileserver

    HOST/fileserver.contoso.com

    This is so we can continue to use Kerberos for authentication. Once this is setup you have to be able use Kerberos for authentication end to end or you will receive an error. If the file server is set up on a cluster as my customer then make sure the virtual name that is being accessed is set to use Kerberos authentication as we did in step #1. No need to set the virtual name here for the trusted for delegation as it is not needed.

    Ok, we should have things setup now. Let’s review what we have done:

    • We enabled the SQL Virtual Network resource for Kerberos authentication.

    We should have the following set to be trusted for delegation: SQL Virtual Network resource name, both of the cluster nodes and the SQL Service Account.

    • We set the proper user rights in the local security policy.
    • We have rebooted both of the cluster nodes.

    Now we can test the Kerberos authentication. Open either the Enterprise Manager or the SQL Management Studio application and run the following command:

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

    It should return Kerberos, if it does then we are successfully authenticating to SQL with Kerberos. Try your insert statement now and it should be able to import if you have the appropriate rights in SQL, the syntax of your command is correct and the format of the file you are importing are correct. If you should need any assistance with this then please contact the SQL Team Blogs which can be found here.

    References:

    956378 FIX: You may be unable to start the SQL Server agent if you configure a SQL Server 2005 failover cluster to use Kerberos constrained delegation for a domain user account

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;956378

    909801 How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;909801

    - Mark ‘DS? No, DB!’ Ramey

  • DelegConfig V2 Released

    Hi all, Ned here again. Our compadre Brian Murphy-Booth has released the newest version of the Kerberos Delegation IIS Website that we have discussed previously here and here.

    You can grab it here:

    http://blogs.iis.net/brian-murphy-booth/archive/2009/04/22/delegconfig-v2-beta.aspx

    Brian really hopes you leave comments and questions, be sure to take him up on his offer. He's good people. :-)

    - Ned Pyle

  • Office 2007 SP2 Administrative Templates Released

    This download includes updated Group Policy Administrative Template and Office Customization Tool OPA files; an updated Office Customization Tool; and ADMX and ADML versions of the Administrative Template files. This update assumes that you have updated your 2007 Office System applications with the 2007 Office System Service Pack 2 (SP2).

    http://www.microsoft.com/downloads/details.aspx?FamilyID=73d955c0-da87-4bc2-bbf6-260e700519a8&displaylang=en

    And if you missed it, the Office 2007 Security Guide was released this week too.

    The 2007 Microsoft Office Security Guide provides prescriptive Group Policy setting and security configuration recommendations to help strengthen the security of computers running the 2007 Microsoft Office release on computers that run Windows Vista or Windows XP in domain–based environments.

    http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=a12eca33-a20d-45e2-895c-5e021f3ae4c5

  • ADAM - Saved event logs show "The description for event ID... cannot be found"

    Hi, Ned here again. Today’s post is a quickie, here’s the scenario:

    Summary

    You are troubleshooting ADAM or AD LDS, probably running on a computer you don’t directly administer. Someone has asked you to examine the saved event logs to see if you can determine what’s going on. They may have even already removed that instance as part of their troubleshooting, and all you have left are the event logs for root cause. So you open the saved EVT or EVTX files in EVENTVWR.EXE and see…

    The description for Event ID ( 700 ) in Source ( ADAM [SMPolicyStore] ISAM ) cannot
    be found. The local computer may not have the necessary registry information or
    message DLL files to display messages from a remote computer. You may be able to
    use the /AUXSOURCE= flag to retrieve this description; see Help and Support for
    details.

    Uhhhhh. Now what?

    Why so weird?

    Saved ADAM and AD LDS event logs need a certain amount of backing information in order to be viewed. Since event logs are saved mainly with the data results to save space, you will need the applications binaries to actually decipher the log completely.

    So you can probably guess what the first step will be, but the next step is trickier.

    Who cares, Ned? Fix it already!

    1. On your Windows Server 2003, Windows XP, or Windows Server 2008 computer, install ADAM or AD LDS.

    (That was probably the part you guessed. But that ain’t all!)

    2. Create an ADAM or AD LDS instance with the same instance name that was used on the previously affected computer. It will be the name in the square brackets repeated in every event message. For example:

    The description for Event ID ( 700 ) in Source ( ADAM [SMPolicyStore] ISAM ) cannot
    be found. The local computer may not have the necessary registry information or
    message DLL files to display messages from a remote computer. You may be able to
    use the /AUXSOURCE= flag to retrieve this description; see Help and Support for
    details.

    Or

    The description for Event ID 1463 from source ADAM [instance1] General cannot
    be found. Either the component that raises this event is not installed on your
    local computer or the installation is corrupted. You can install or repair
    the component on the local computer.

    3. You don’t have to change the defaults anywhere when you configure the instance except the instance name itself – i.e. no need to change the service account, create a partition, change the Administrator Role credentials, or import any LDF files.

    So after having added an ‘empty’ instance called “Instance1”, I can now open the saved EVTX file and I see:

    Log Name:      ADAM (instance1)
    Source:        ADAM [instance1] General
    Date:          4/27/2009 12:32:27 PM
    Event ID:      1463
    Task Category: Internal Configuration
    Level:         Warning
    Keywords:      Classic
    User:          ANONYMOUS LOGON
    Computer:      2008-srv-03.fabrikam.com
    Description:
    Active Directory Lightweight Directory Services has detected and deleted some possibly corrupted indices as part of initialization.

    These deleted indices will be rebuilt.

    Ahhh, that’s more like it. Until next time.

    - Ned ‘Event Coordinator’ Pyle

  • New Directory Services KB Articles 4/19-4/25

    New KB articles related to Directory Services for the week of 4/19-4/25.

    956114

    You cannot use a UPN-formatted user name to log on to a Windows Server 2003-based FTP server that is running IIS 6.0 if the domain controller for authentication is running Windows Server 2008

    957072

    Windows Server 2008-based domain controllers cannot negotiate Quality of Protection (QoP) for Transport Layer Security (TLS) connections from non-Windows LDAP clients

    970435

    You may see garbled text on the logon page when you connect to a Terminal Services RemoteApp application from the Administration Console for Windows Essential Business Server 2008 Management Server

  • Breaking Down DCDiag.exe to an Object with PowerShell

    An interesting take on “parse and pray” with PowerShell against a DCDIAG output text file.

    http://bsonposh.com/archives/723

  • Working with Certificates in Active Directory PowerShell

    http://blogs.msdn.com/adpowershell/archive/2009/04/26/working-with-certificates.aspx

  • Recovering a Deleted Cluster Name Object (CNO) in a Windows Server 2008 Failover Cluster

    http://blogs.technet.com/askcore/archive/2009/04/27/recovering-a-deleted-cluster-name-object-cno-in-a-windows-server-2008-failover-cluster.aspx