Azure Sql Database Login Failed for User
- Home
- Databases
- Microsoft SQL Server
Hi,
I am in a situations of restoring an application server to a new machine.
The application is having a MS SQL database. I have restored the SQL database on the new server, and installed the application etc.
But When I connect the client application to login, it give Login Failed error.
I can see the Database have some users, and they are not available on the newly installed sql server's security options.
I have also the created similar usernames and granted the similar permissions on the SQL server, but I dont have the password for the user id listed in the database.
How can I retrieve the password from the Database, or How can I add the user id from the Database to the server; I mean restore the user credential from the database.
Totally losted with this Login failed error.
So you need to move the database to a new server with the same login id, on the new sql.
If you have the access to the old sql server, try get the password hash from it.
SELECT [ name ],[ password_hash ] FROM sys . sql_logins
or if you know login Id eg. for sa:
SELECT [ name ],[ password_hash ] FROM sys . sql_logins where name = 'sa'
Copy the Hash code from result of the query.
Then on the New server create the same login id with the same encrypted password. Replace the hash code you just copied with <password-hashes-here> in the below statement and run on the new sql.
CREATE LOGIN sa WITH PASSWORD = < password - hashes - here > HASHED ;
9 Replies
Database logins and server logins are different objects, albeit related. A server login is kept with themaster database and a database login with the user database. If you only restore the user database and not the master database, you'll end up with those orphaned logins. There are several options available, but they all depend on your setup, which you didn't care to explain.
I am trying to restore the database to a new server, with new sql 2005 installation.
And I dont have the password for the username., So how can I get the database login into the server login.
mutallibk wrote:
I am trying to restore the database to a new server, with new sql 2005 installation.
And I dont have the password for the username., So how can I get the database login into the server login.
If the logins are Windows credentials the passwords the users already know as it will be their normal login ones.
If the passwords and usernames are SQL users, just give them a new password and when the applications tries to open it should ask for details.
Brand Representative for Microsoft
If you are a member of the local administrators group on the server, you can gain access to SQL Server. SQL Server has an emergency back door that you can access by restarting it in single-user mode. When in single-user, you can then add yourself as a login and add the login to the sysadmin group. The restart SQL normally, and you have full access.
When I restart SQL in single-user mode, I like to specify that the single user has to be the SQLCmd command line application to ensure that nothing else can take the single connection. Instead of specifying just the -m startup flag, you would specify -m"SQLCmd".
Steps to take:
- Stop SQL Server manually using SQL Server Configuration Manager (SSCM)
- The SQL Server Agent will be stopped automatically. Stop any other services that may be running like SSIS
- Right-click on the SQL Server service in SSCM and click on Properties
- Add the -m"SQLCmd" startup parameter in the Startup Parameters tab
- Click OK
- Start the SQL Server service in SCCM (do not start the SQL Server Agent)
- Open a command window (Start -> Run -> cmd)
- Connect to the server using SQLCmd
- Type in: Create Login [Domain\UserName] From Windows; (replacing Domain\UserName with our Windows account)
- Hit ENTER
- Type in: Go
- Hit Enter
- Type: Exec sp_addsrvrolemember 'Domain\UserName','sysadmin';
- Ht ENTER
- Type: Go
- Hit Enter
Note that I connected to a named instance (SQLMCMLap\SQL14). For the default instance, I don't need to specify a name at all or I could just have entered the server name.
- Close command window.
- Stop the SQL Server service via SSCM
- Edit the SQL Server service properties to remove the startup parameter you added on the Startup PArameters tab and click OK
- Start the SQL Server service
- Start the SQL Server Agent service
- Start any other SQL services you stopped
EDIT: Created a how-to for this:
http://community.spiceworks.com/how_to/118087-how-to-gain-access-to-sql-server-when-nobody-has-acces...
Edited May 10, 2015 at 15:09 UTC OK, the SQL login is hardcoded in the Application, and uses that particular user id, I have changed the sa password, and also for the odbc i have used the sa, but when I run the application I get the error 18456, kind.
I need to import/restore the user login ID from the Database to the system, or the sql instance.
Also This is to move the database from the old server to the new server. and Connect the Clients to the new sql server. As the Application cannot be modified, or altered any configuration (as we dont have the source code etc, vendor support license expired long time ago.)
So you need to move the database to a new server with the same login id, on the new sql.
If you have the access to the old sql server, try get the password hash from it.
SELECT [ name ],[ password_hash ] FROM sys . sql_logins
or if you know login Id eg. for sa:
SELECT [ name ],[ password_hash ] FROM sys . sql_logins where name = 'sa'
Copy the Hash code from result of the query.
Then on the New server create the same login id with the same encrypted password. Replace the hash code you just copied with <password-hashes-here> in the below statement and run on the new sql.
CREATE LOGIN sa WITH PASSWORD = < password - hashes - here > HASHED ;
What application are you attempting to use? I know from previous experience when I moved SQL versions the ERP software I was using had to manually re-create the users into an updated table, by using the original table for reference.
To transfer SQL Logins from one server to another then follow this Microsoft article.
https://support.microsoft.com/en-us/kb/918992
This will set-up the logins with the same SIDs and therefore not orphan the database users.
Brand Representative for Microsoft
For SQL logins, you also have to make sure that the SID (security Identifier) of the login is the same as the SID of the database user. Users map to logins on SID, not name.
This script I wrote takes care of a passwords, SIDs, server level permissions, and server role memberships. It's a lot more thorough than the others: Transferring Logins
This topic has been locked by an administrator and is no longer open for commenting.
To continue this discussion, please ask a new question.
Azure Sql Database Login Failed for User
Source: https://community.spiceworks.com/topic/943312-application-getting-login-failed-for-user