2

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.

Improve this question
1
  • What happens if you define a password for the login gp you created and configure that password on the ODBC creation? Does the error change?
    – Ronaldo
    Commented Apr 17, 2020 at 11:27

1 Answer 1

Reset to default
2

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.

Improve this answer
9
  • I believe the method you described shall work only for SQL Server local logins, but the question says "Currently it works with Windows Authentication on a server running Windows Server and Microsoft SQL Server". When Windows Logins are configured, no passwords are stored on SQL Server to be retrieved and he would only need to create a login on the new instance based on the same Windows login. Am I wrong?
    – Ronaldo
    Commented Apr 15, 2020 at 15:22
  • You are partly correct: It will work for SQL Server Logins and Windows Authenticated Logins that have been created in SQL Server. However, I deduced from his last paragraph, that he was having issues starting the application. Hence my suggestion, that the gp SQL Server login be transferred from the old instance to the new instance.
    – John K. N.
    Commented Apr 15, 2020 at 19:08
  • You are right Ronaldo, and thank you @Johnakahot2use I think what you are pointing me to interesting direction, Now I wonder the programmer created a gp account with password in the application, if so we will know. Thanks a lot will work on it and update you.
    – abhay9
    Commented Apr 15, 2020 at 19:28
  • I run the script on the Source Server, on running EXEC sp_help_revlogin I got list of Login users, but non of them were user: gp. When I look into database used by program there is a gp user type created with "SQL user without login".
    – abhay9
    Commented Apr 16, 2020 at 9:45
  • Are there any other database users in the source database? When you open up the tree Databases | <Source Database> | Security | Users what do you see in there? You should see dbo, guest, INFORMATION_SCHEMA, sys and possibly the gp user. Any other users?
    – John K. N.
    Commented Apr 16, 2020 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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