SQL Server Authentication – Login Without Password

authenticationsql server

I need to login to MS Sql from ODBC to my MS Sql instance on Linux server. Following are my question

  1. Is there a way to do it without Active Directory (maybe with LDAP or ADLDS if it does not change user profile)?

  2. I created a SQL Server Login without password by deselecting enforce password policy. I can finish adding ODBC and Test Connection is successful, but when I run the program it gives an error the the login has failed and in the SQL Server Log it shows:

    Login failed for user 'gp'. Reason: Password did not match that for the login provided".

The program I am running is a old program and I don't have the source code to change anything in it. Currently it works with Windows Authentication on a server running Windows Server and Microsoft SQL Server.

Best Answer

You might want to have a look at How to transfer logins and passwords between instances of SQL Server to migrate the gp user from the old SQL Server instance to the new SQL Server instance.

Analysis

The application will be logging in with the gp account and a pre-defined password stored in the application.

Because you have not provided a password for the new user you created on the new instance the login is failing.

Solution

You will have to create the SQL Server login with the above mentioned method provided by Microsoft. Essentially the scripts create two procedures on the source instance in the master database. When you execute EXEC sp_help_revlogin it creates an output containing a CREATE LOGIN ... statement for each SQL Server Login which contains the SID and encrypted password of the original logins.

The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

You then create the required SQL Server Login on the target SQL Server Instance by running the corresponding CREATE LOGIN.... statement for the gp login on the target instance.

Related Question