aiDeeva.com

Author: Jugal Shah

  • Join me on SQLPass.Org

    Dear Readers,

    You can join me on SQLPass

    http://www.sqlpass.org/Community/PASSPort.aspx?ProfileID=42686

     

  • Service Principle Name

    SPN (Service Principle Name)

    What is SPN? A service principal name (SPN) is the name by which a client uniquely identifies an instance of a service. The Kerberos authentication service can use an SPN to authenticate a service. When a client wants to connect to a service, it locates an instance of the service, composes an SPN for that instance, connects to the service, and presents the SPN for the service to authenticate.

    How the SPN works or what is the internal process of SQL Server to register SPN?When SQL Server service is started, it first checks if SPN exists and if no SPN found calls API to create new SPN. At the shutdown service sends request to delete the SPN. 

    What if SQL Server is running under local administrator account? Can it register SPN?No, it cannot. The account must have domain administrator rights to register the SPN 

    Can we register SPN for other services as well? Yes, you can register the SPN for the other services as well. (i.e Analysis Service) 

    How can I see the SPN for my different SQL Boxes? First you need to download the SPN tool from below URL http://www.microsoft.com/downloads/details.aspx?FamilyId=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en

     How to use this utility, what are the commands available for it?Please find the SPN Syntax as below.

    Syntax setspn [switches data] computername 

     Note: Computer Name can be the name or domain\name

     Switches:
       -R = reset HOST ServicePrincipalName
        Usage:   setspn -R computername
       -A = add arbitrary SPN
        Usage:   setspn -A SPN computername
       -D = delete arbitrary SPN
        Usage:   setspn -D SPN computername
       -L = list registered SPNs
        Usage:   setspn [-L] computername

    Examples:
    setspn -R daserver1
       It will register SPN “HOST/daserver1 and “HOST/{DNS of daserver1}”
    setspn -A http/daserver daserver1
       It will register SPN “http/daserver” for computer “daserver1”
    setspn -D http/daserver daserver1
       It will delete SPN “http/daserver” for computer “daserver1”

    How should I use this utility SQL Server Services?You can use below commands to register different services 

    To Register SQL Server Database Service

    SetSPN –A MSSQLSvc/serverHostName.Fully_Qualified_domainName:[TCP Port Number] [Account Name]

    Note: You can use the same command Named Instance as well

    To Register Analysis Service

    Setspn.exe -A MSOLAPSvc.3/serverHostName.Fully_Qualified_domainName OLAP_Service_Startup_Account

    How to check whether SQL Server Services are registered or not?

    You can use below command.

    SetSPN –L [AccountName]

    What happens if SQL Server Service fails to create SPN during startup?

    If the SQL Server Service is failed to create and register SPN, it will log the error in the error log during startup. Moreover you will get the below type of issues from client side

    You will get the connectivity issues from client side as below

    Error: 18456, Severity: 14, State: 11.
    Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.

    How to check after SPN registration SQL Server Kerberos authentication?

    Check the auth_scheme column by executing below query

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

    What are the best practices for SPN in cluster environment? In cluster server configure both nodes to use same network DC

  • Script to Verify the litespeed backup file

    Use the below script to verify the litespeed backup file

    exec master.dbo.xp_restore_verifyonly
    @filename = N'\\backups\full\mydbbackup.full.BAK',
    @filenumber = 1,
    @logging = 0

  • Script to list out important properties of database

    Use below script to list out the important properties of the database

     


    select
    sysDB.database_id,
    sysDB.Name as 'Database Name',
    syslogin.Name as 'DB Owner',
    sysDB.state_desc,
    sysDB.recovery_model_desc,
    sysDB.collation_name,
    sysDB.user_access_desc,
    sysDB.compatibility_level,
    sysDB.is_read_only,
    sysDB.is_auto_close_on,
    sysDB.is_auto_shrink_on,
    sysDB.is_auto_create_stats_on,
    sysDB.is_auto_update_stats_on,
    sysDB.is_fulltext_enabled,
    sysDB.is_trustworthy_on
    from sys.databases sysDB
    INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid

  • Script to change the database compatibility level

    Use below script to change the database compatibility level

    ALTER DATABASE DatabaseName
    SET SINGLE_USER
    GO
    EXEC sp_dbcmptlevel DatabaseName, 90;
    GO
    ALTER DATABASE DatabaseName
    SET MULTI_USER
    GO

    SQL Server Version Compatibility Level
    SQL Server 6.5 65
    SQL Server 7.0 70
    SQL Server 2000 80
    SQL Server 2005 90
    SQL Server 2008 100