I have a database storing user login credentials of my application in a Table. When I backed up and restored my database to another server, the restore is successful but the login is timing out (SQL Server timeout). Even though I increased the timeout, still no luck.Can anyone suggest what can be the reason for that.

Can re-indexing the database help me in the new server?

  • Restore only transfers users and not logins, I hope you are aware of this. – Learning_DBAdmin Dec 3 at 7:27
  • I have a table storing User name and hashed password to my application.This table is causing timeout – mrinali Dec 3 at 7:32
  • 4
    If you get a login timeout, then it has nothing to do with your database or your restore. If you indeed get a timeout, then the client cannot find the server in the first place. If you edit your post and add the exact error message, we might be able to know what is actually going on. – Tibor Karaszi Dec 3 at 7:35
  • As suggested by @TiborKaraszi, Please also check whether you are able to do telnet from your DB server to application server on the SQL port(1433 if default). – Learning_DBAdmin Dec 3 at 7:37
  • 1
    Please edit your question and include the details which you have mentioned in the comment. – Learning_DBAdmin Dec 3 at 7:38

What my colleagues are attempting to say in the comments, is that, they / we are making an assumption that the issue is due to the account / sql login not existing on your new server (the one where you restored your database). The most likely cause of what is going on is that since the login / sql account you use on your original server doesn't exist on the new server it's causing your application / connection to time out.

No biggie... easy fix with two options that come to mind to help resolve the issue.

First option - Manually create a new user with the same name and password then run the following script on the database you restored:

sp_change_users_login 'update_one', '[yourDBuser]', '[yourSQLlogin]'

This will connect the newly created SQL Server login to the user in the database you are having issues with.

Second option (I prefer this one) - Install / create the sp_help_revlogin stored procedure on your original server. You can find the script here.

Once it's installed execute the following:

sp_help_revlogin '[yourSQLlogin]'

This will generate an output like so:

-- Login: yoursqllogin
CREATE LOGIN [yoursqllogin] WITH PASSWORD = 0x0200EF889B2ECBF4A17B836A62AE80BC0F718EBF117C73697892B112C3744D04AB18490E8C666C1154F42BC3D0C49346843032AFBFBEBA28CFE018AB1FED25EF2830A5F046E9 HASHED, SID = 0x01, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF

Copy and paste the output onto your new server, and you will be rockin' and rollin' in no time.

  • Thanks for the update...Will try and check if it works :) – mrinali Dec 3 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.