.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

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

Posted By:Dhiraj Ranka       Posted Date: October 06, 2010    Points: 50    Category: DataBase    URL: http://www.dotnetspark.com  

Most often error came across when changing the 'sa' password. This article will give the solution of the error.
 

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

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

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.

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend