Cannot set a credential for ‘sa’ (SQL Server 2005/2008)

As best security guidelines today I was changing the password for ‘sa’ account using SQL Server Management Studio (SSMS) and unexpectedly I got an error message.

Msg 15535, Level 16, State 1, Line 1
Cannot set a credential for principal ‘sa’.

I was not at all expecting this. I tried a lot of other options like logging through windows account and trying to change the password for ‘sa’, but no luck. Then finally landed on Microsoft’s this KB article for SQL Server 2008 which mention that it has a “fix”. You receive an exception in SQL Server 2008 when you try to modify the properties of the SQL Server Administrator account by using SQL Server Management Studio.

When I followed all the instructions mentioned in the article, it worked perfectly fine. Following is the screen shots of normal try of password change.

Login properties

Login properties

As developer you shouldn’t give so early, so you will try to script the same thing. So even I did the same. Following is the script I tried to run in query window.

USE [master]
GO

ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[LIFTest], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON, NO CREDENTIAL
GO

USE [master]
GO

ALTER LOGIN [sa] WITH PASSWORD=N'JH@46O!IH#NFi349gh'
GO

But still no luck 🙁

Generally this issue arises if the “Map To Credential” check box is not selected on the General tab of dialog box opened while changing the parameter for ‘sa’ account. If this check box is not selected, SQL Server 2008 will try to drop any credential that is mapped to your login credentials. Because your credentials cannot be set to the “sa” login, you receive the exception error message that is mentioned in the “Symptoms” section when you click OK to close the dialog box.

In order to resolve this issue one just has to make sure that you select the “Map To Credential” check box as shown in following screen shot.

login properties map

Login properties

I know you will find it’s little weird, that how “Map to Credential” is selected and yet there is no credential provided. But that’s all what one have to do.

Tagged with: , , ,