Sql-server – Getting ‘specified network password is not correct’ when trying to change password

sql server

After changing my network password, I went straight to the SQL Server Configuration Manager (SQL Server 2008) to update the passwords for the SQL Server and SQL Agent services. (Background info: the services run under my domain account.)

However, I kept getting the below error after clicking on apply:

The specified network password is not correct. [0x80070056]

I guarantee the password entered was definitely the correct one because I have restarted my workstation and used it to log on successfully. Most answers on the web tend to focus on the password but I know that's just the symptom not the root cause.

I know it is difficult to answer questions like this one unless you have experienced it first-hand. So I am asking if anyone else has seen this behavior before and what they did to resolve it.

Best Answer

I have resolved it. Below is what I did and what fixed it.

Ran "net user /domain " and made sure the account was still active and not locked or expired. (The account was active and healthy)
Restarted the workstation. (The only thing it proved was the password was correct and I managed to log on successfully)
Waited for couple of hours. (Sometimes you just have to be patient.)

After doing all of the above, the issue still remained. In the end, it was the action below that resolved it:

I went to the Services mmc snap-in under Control Panel -> Administrative Tools instead of SQL Server Configuration Manager. Changed the password there and started the SQL Server service successfully. Repeated the steps for SQL Agent.

It worked but I am still puzzled why it worked in Services but not in SQL Server Configuration Manager. Active Directory vs WMI? Anyone?

Related Solutions

How to Specify a Linked Server to Remote Database Over TCP/IP

I don't see a reason why you should be using a DSN. Create the linked server using the SQL Server Native Client directly:

EXEC master.dbo.sp_addlinkedserver
    @server     = N'MAIN',
    @srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer”
    @provider   = N'SQLNCLI', 
    @datasrc    = N'I .P_Address';

Then you can create the local/remote login pairs:

-- Pair local and remote logins
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname  = N'MAIN',
    @useself     = N'False',
    @locallogin  = N'Local_user_name',
    @rmtuser     = N'Remote_user_name',
    @rmtpassword = N'Remote_password';v

Sql-server – How does “Be made using the login’s current security context” pass the users password to remote server

Whilst liasing with Microsoft on a different issue (paid support request) i happened to ask them about this & they confirmed the unhashed password is passed to the remote server but the mechanism in which the SQL Engine does this is "hidden and cannot be captured" - but suffice to say, the login is not done using the hash.

Here is there full response:

How does the SQL Server provide the correct password (when dealing with non-windows credentials) to the other server when all the server has is a Hash?

Answer: If connected to the local server using SQL Server Authentication, login name and password will be used to connect to the remote server. In this case a login with the exact same name and password must exist on the remote server.

Be made using the login's current security context:

Specify that a connection will be made using the current security context of the login for logins not defined in the list. If connected to the local server using Windows Authentication, your windows credentials will be used to connect to the remote server. If connected to the local server using SQL Server Authentication, login name and password will be used to connect to the remote server. In this case a login with the exact same name and password must exist on the remote server.

How does the SQL Server provide the correct password (when dealing with non-windows credentials) to the other server when all the server has is a Hash?

Answer: Login name and password is passed to the remote server, this mechanism/task is handled by the SQL Engine which is hidden and cannot be captured.

If SQL only has a hash available - can it logon to the remote server using the hash? Or is the original (un-hashed) password kept in the user’s session / memory for the entire duration of their connection which SQL can then retrieve and pass to the remote server for login? If the server can login with a hash instead of the password – can this be done in normal logins or is it purely an internal feature of linked servers?

Answer: HASH cannot be used to login to the SQL server, SQL Engine is responsible for handling login process and we don’t have much documentation around this.

Best Answer

Related Solutions

How to Specify a Linked Server to Remote Database Over TCP/IP

Sql-server – How does “Be made using the login’s current security context” pass the users password to remote server

Related Question