Dear Readers,
You can join me on SQLPass
http://www.sqlpass.org/Community/PASSPort.aspx?ProfileID=42686
Dear Readers,
You can join me on SQLPass
http://www.sqlpass.org/Community/PASSPort.aspx?ProfileID=42686
Posted in Microsoft SQL Server | Tags: SQLPass
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
Posted in Microsoft SQL Server | Tags: Service Principle Name, SPN, SQL Server 2005
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
Posted in Microsoft SQL Server | Tags: litespeed, quest, Script, verify backup
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
Posted in Microsoft SQL Server | Tags: Database Properties, Script
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 |
Posted in Microsoft SQL Server | Tags: database Compatibility, Script
Problem:Login failed for user XYZ The user is not associated with a trusted SQL Server connection.
Resolution: Login is failed due the the authentication mode is Windows Authentication and it is not allowing SQL Server authentication for SQL Login.
Change the Authentication Mode of the SQL server from Windows Authentication Mode (Windows Authentication) to Mixed Mode (Windows Authentication and SQL Server Authentication)
Right Click on Server and Select the Server Properties from pop up.

Posted in Microsoft SQL Server
Problem: Unable to install Windows Installer MSP file
Description: I got the above error while installing Service Pack3 on SQL Server 2005 box.
Product : Database Services (INST2)
Product Version (Previous) : 3257
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB955706_sqlrun_sql.msp.log
Error Number : 11032
Error Description : Unable to install Windows Installer MSP file
Resolution:
Step 1: Check the setup log file from C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB955706_sqlrun_sql.msp.log
Step 2: Search for “return value 3” and scan next +/- 10 lines for error details
Step 3: During scan I found the below error details “Fail create file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData folder” I have created the required folder at desired location and has resolve the issue.
There are other causes as well for the failure to resolved it you can follow the below steps
- Copy the service pack from network location to installation location or on SQL Server box
- Check for the registry parameters
Check below KB documents to resolve error
http://support.microsoft.com/kb/926622.
Posted in Microsoft SQL Server | Tags: Service Pack 3 Installation
Error Description
OLE DB provider “SQLNCLI” for linked server “MYView” returned message “The transaction manager has disabled its support for remote/network transactions.”.
Msg 7391, Level 16, State 2, Line 23
The operation could not be performed because OLE DB provider “SQLNCLI” for linked server “MYView” was unable to begin a distributed transaction.
Resolution:
1. Check whether DTC is blocked by firewall, if it is blocked by firewall release it.
2. Check or configure DTC to allow network connection as below.

Go to Control Panel ->Administrative Tools -> Component Services -> Expand the Computer Node -> Right click on My Computer) -> Click on Properties -> MS DTC Tab -> Security Configuration
Posted in Microsoft SQL Server
You can use below query to generate DBCC Shrikfile command for data and log file.
Query
select 'use ' + db_name(dbid) + char(13) + 'dbcc shrinkfile (' + quotename(sf.name,'''') + ' ,truncateonly)' from sysaltfiles sf
inner join sys.databases sd on sf.dbid = sd.database_id
where state_desc = 'online'
Posted in Microsoft SQL Server | Tags: DBCC Shrinkfile
Error: Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection.
Error clearly state that login is not associated with the trusted connection.
What is trusted connection and what is the advantage of it? Integrated authentication allows for SQL Server to leverage Windows NT authentication to validate SQL Server logon accounts. This allows the user to bypass the standard SQL Server logon process. With this approach, a network user can access a SQL Server database without supplying a separate logon identification or password because SQL Server obtains the user and password information from the Windows NT network security process.
Advantage of this process is that we don’t have to store credential with the connection string.
However sometimes you will see the error above error.
Resolution

2. Check the user/login is associated with the appropriate SQL login
3. Host is in the same domain or Host machine is network, sometime rebooting of the host machine will resolve this issue.
Posted in Microsoft SQL Server | Tags: Login Issue