ERROR - Cannot set a credential for principal 'sa'

You may sometimes receive an error which says "Cannot set a credential for principal 'sa'" while you are trying to alter the login 'sa' in SQL Server 2005 or SQL Server 2008.

This happens because, the SQL Server tries to drop any credential that is mapped to your login credentials while altering and because your credential cannot be set to "sa" login, you will receive this error.

FIX:
Fix for this error is very simple. Just make sure that the option "Map to Credential" is checked in the "General" tab of the Login Properties Page.

Reference: http://support.microsoft.com/kb/956177

SQL Server 2011 (Denali) - First Look

First look of SQL Server Denali after Installation.

Splash Screen

Login Window

SQL Server Management Studio

SQL Server 2011 (Denali) - Installation

The Installation process of SQL Server Denali is similar to the installation of SQL Server 2008 with little bit of modification to accommodate the new features installation.

Surface Area Configuration in SQL Server 2008

When I ask the question, what is the difference between SQL Server 2005 and SQL server 2008, one of the differences told by many people is that the Surface Area Configuration has been removed in SQL Server 2008.
But in reality the options that were managed using the Surface Area Configuration tool in SQL Server 2005 are now being managed using Facets in Policy Based Management in SQL Server 2008 onwards.

Facet in general means “a predefined set of properties that can be managed”

To access the Surface Area Configuration in SQL server 2008 onwards, follow the steps below.

1.  Right Click on the Server and choose "Facets"
   
   
   
2. In the resulting page, choose the facet “Surface Area Configuration” to manage the its properties
   

T-SQL Queries to find SQL Server Cluster Nodes and Shared Drives

In one of my earlier post's "Finding Cluster Nodes or Cluster Name" , I had told about the query which can be used to find the name of the node on which the SQL Server Instance is currently running.
Here are more such SQL Server cluster related T-SQL queries which helps in finding SQL Server Cluster Nodes and their shared drives.

1. Find name of the Node on which SQL Server Instance is Currently running
   

   SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName]

   If the server is not cluster, then the above query returns the Host Name of the Server.
   
   
2. Find SQL Server Cluster Nodes
   a. Using Function
   

   SELECT * FROM fn_virtualservernodes()

   
   b. Using DMV
   

   SELECT * FROM sys.dm_os_cluster_nodes

   
   
3. Find SQL Server Cluster Shared Drives
   a. Using Function
   

   SELECT * FROM fn_servershareddrives()

   
   b. Using DMV
   

   SELECT * FROM sys.dm_io_cluster_shared_drives

Configuring Change Data Capture (CDC)

Change Data Capture (CDC) is a new feature that is introduced in SQL Server 2008. CDC provides the ability of database level auditing.
Using CDC we can capture the DML operations like Insert, Update and Delete statements.
Enabling CDC is done at database level first and then on the required tables. Once CDC is enabled, a similar table is created in the database with the schema “cdc” and this table will store the Tracked data.

Enabling Change Data Capture

Check if the database is enabled for CDC

/* Check CDC Status of the Database */ SELECT name AS DatabaseName        ,database_id AS DatabaseID        ,CDCStatus= CASE WHEN is_cdc_enabled=1 THEN ‘Enabled’                            ELSE ‘Disabled’                            END FROM sys.databases WHERE name=‘CDC_AdventureWorks’

Enable CDC on the database

/* Enable CDC */ USE CDC_AdventureWorks GO Exec sys.sp_cdc_enable_db GO

Once the CDC is enabled successfully, then you can see two new jobs (“cdc.<DatabaseName>_capture” and “cdc.<DatabaseName>_cleanup”) and one new schema named ‘cdc’.

Enable a Table for CDC. For this I am choosing a small table.

/* Enable CDC on Table */ USE CDC_AdventureWorks GO EXEC sys.sp_cdc_enable_table @source_schema = N’HumanResources’, @source_name   = N’Shift’, @role_name     = NULL GO

Once the CDC is enabled for a table the CDC jobs will be started



Also you can notice that the capture job runs continuously to capture the changes.

You can now verify that a new change tracking table will created.

/* Verify CDC tables */ SELECT SCHEMA_NAME(schema_id) AS SchemaName        ,name AS TableName FROM sys.objects WHERE name like ‘%Shift%’ and type=‘U’

The table “HumanResources_Shift_CT” with the schema name “cdc” from now will hold the tracked data.
Also you can notice that the newly created table will have 5 additional Columns to help capturing the changed data
These Columns are:
__$start_lsn
__$end_lsn
__$seqval
__$operation
__$update_mask



Testing Change Data Capture
As we know that the CDC captures inserts, deletes and updates we will start testing all the possibilities.

Testing insert Operation
When you insert data to the main table the CDC will capture one corresponding record for each insert.

/* Testing Insert */ INSERT INTO HumanResources.Shift VALUES (4,’LateEvening’,’1900-01-01 18:00:00.000’,’1900-01-01 02:00:00.000’,GETDATE()) SELECT * FROM  HumanResources.Shift SELECT * FROM cdc.HumanResources_Shift_CT

Testing Update Operation
When you update data in the main table, the CDC will capture two corresponding records for each update. One record will have values Before Update and the other will have values after update.

/* Testing Update */ UPDATE HumanResources.Shift SET ShiftID=5 WHERE ShiftID=4 SELECT * FROM  HumanResources.Shift SELECT * FROM cdc.HumanResources_Shift_CT

Testing Delete Operation
When you delete data from the main table the CDC will capture one corresponding record for each delete.

/* Testing Delete */ DELETE FROM HumanResources.Shift WHERE ShiftID=5 SELECT * FROM  HumanResources.Shift SELECT * FROM cdc.HumanResources_Shift_CT

Auditing the Tracked Data
Now after all the changed data is captured, it’s time to audit the Tracked Data.
Below query will get you all the changes done to the HumanResource.Shift Table in the last 24 Hours.

/* Get all changes to HumanResources.Shift table in last 24 Hrs*/ DECLARE @begin_lsn BINARY(10), @end_lsn BINARY(10); SELECT @end_lsn= MAX(start_lsn) FROM cdc.lsn_time_mapping where tran_begin_time<= GETDATE() and tran_end_time >= GETDATE()-1 SELECT @begin_lsn= MIN(start_lsn) FROM cdc.lsn_time_mapping where tran_begin_time<= GETDATE() and tran_end_time >= GETDATE()-1 SELECT * FROM cdc.fn_cdc_get_all_changes_HumanResources_Shift(@begin_lsn,@end_lsn,’all’) GO

Disabling the CDC

/* Get the list of tables involved in CDC */ USE CDC_AdventureWorks; GO EXEC sys.sp_cdc_help_change_data_capture GO

/* Disable CDC on Tables */ USE CDC_AdventureWorks; GO EXECUTE sys.sp_cdc_disable_table     @source_schema = N’HumanResources’,     @source_name = N’Shift’,     @capture_instance = N’HumanResources_Shift’; GO

After Disabling the CDC on the table, you can notice that the table “cdc.HumanResources_Shift_CT “ which was created for tracking changes will no more exist.

/* Disable CDC on Database */ USE CDC_AdventureWorks; GO EXEC sys.sp_cdc_disable_db GO

Once the CDC is disabled on the database, then you will notice that the schema by name “cdc” and the other CDC related tables, SP’s, functions and Jobs will no more exist.

Error while configuring Change Data Capture (CDC)

I was trying to configure Change Data Capture (CDC) on my system a while ago and I noticed that i did not had a test database on the newly installed Instance. So I backed up the AdventureWorks database from an existing instance on my laptop and restored on the new Instance.

Okay, Now after the restoration, I executed "Exec sys.sp_cdc_enable_db" command to enable CDC on the database but the database did not allow me to enable CDC by giving the below error.

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 186 Could not update the metadata that indicates database CDC_AdventureWorks is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request. Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db_internal, Line 0 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1. Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db, Line 0 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1. Msg 3998, Level 16, State 1, Line 1 Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

This is because, the Database Owner was Invalid on the new Instance as the Login that was DBOwner in the other Instance was not present in the new Instance.

SELECT name

        ,database_id

        ,USER_NAME(owner_sid) as DBOwner

FROM sys.databases WHERE name ='CDC_AdventureWorks'

Fix: To Fix this error, change the owner of the database to 'sa' or a valid Login.

Use CDC_AdventureWorks

GO

Exec sp_changedbowner 'sa'

GO

Once you change the database owner to 'sa' or a valid Login you should be now able to enable CDC on that Database.

Different ways to find the SQL Server Port Number

We all know that SQL Server by default listens to the port number 1433. But, due to security reasons, the default port number will/can be changed to a different port in order to minimize hacks on SQL Server.

There are many ways using which you can find the port number of the SQL server which it is listening to.

1. Using the SQL Server Configuration Manager
2. Using Registry
3. Using Error Log
4. etc...

Using SQL Server Configuration Manager:
Click Start >> Run
For SQL Server 2005 Type "SQLServerManager.msc" and click OK
For SQL Server 2008 Type "SQLServerManager10.msc" and click OK

This will Open the SQL Server Configuration Manager for you.
Now in the Left Pane, expand "SQL Server Network Configuration" and click on "Protocols for <InstanceName>"
Now In the right pane, right-click on the "TCP/IP" protocol and go to properties

In the TCP/IP properties, go to the "IPAddresses" Tab and scroll down and locate the section "IPAll"
In the "IPAll" Section, the "TCP Port" represents the Port Number SQL Server is listening to.

Note: <InstanceName> is your SQL Server Instance Name

Using Registry:
To find the SQL Server Port number using registry, execute the below query.

DECLARE @InstanceName nvarchar(50) DECLARE @value VARCHAR(100) DECLARE @RegKey_InstanceName nvarchar(500) DECLARE @RegKey nvarchar(500) SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),'MSSQLSERVER')) --For SQL Server 2000 if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))=8 BEGIN if @InstanceName='MSSQLSERVER' Begin SET @RegKey='SOFTWARE\Microsoft\'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP\' END ELSE BEGIN SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP\' END EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'TcpPort',   @value = @value OUTPUT   Select @@SERVERNAME as ServerName,@value as PortNumber END --For SQL Server 2005 and up if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8 BEGIN SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL' EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey_InstanceName,   @value_name = @InstanceName,   @value = @value OUTPUT SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP\IPAll' EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'TcpPort',   @value = @value OUTPUT   Select @@SERVERNAME as ServerName,@value as PortNumber END

Using Error Log:

To find the SQL Server Port number using error Log, execute the below query.

SET NOCOUNT ON if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))=8 BEGIN Create Table ##ErrorLog_2K (ErrorLog nvarchar(1000), ContinuationRow int ) INSERT INTO ##ErrorLog_2K Exec master..xp_readerrorlog SELECT DISTINCT @@SERVERNAME as[ServerName] , SUBSTRING(RIGHT(ErrorLog,5),1,4) as [PortNumber] FROM ##ErrorLog_2K where ErrorLog like '%SQL Server listening on 1%' DROP TABLE ##ErrorLog_2K END if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8 BEGIN Create Table ##ErrorLog (Logdate datetime, ProcessInfo nvarchar(100), [Text] nvarchar(1000)) INSERT INTO ##ErrorLog exec master..xp_readerrorlog SELECT DISTINCT @@SERVERNAME as[ServerName] , SUBSTRING(RIGHT(text,6),1,4) as [PortNumber] FROM ##ErrorLog where text like 'Server is listening on % ''any'' %' DROP TABLE ##ErrorLog END SET NOCOUNT OFF

You can download the scripts from here.

1. SansSQL_FindPortNumber_UsingRegistry.sql
2. SansSQL_FindPortNumber_UsingErrorLog.sql