none
SQL Server Authentication

    Question

  • I need help to clarify the following:

    I developed a script to get data from an SQL Express database using a SQL user authentication. The ODBC link is configured as System DNS and with SQL Server authentication. (ID: “ADMIN”; Password:”1234”)

    To run the script, I’m using an application. The application, the script and the SQL express database are in the same computer.

    The computer login is done using a user domain account (ex: “OPERATOR1”) , without administrator rights, and this user (“OPERATOR1”) is not configured in SQL.

    Question: If I run the application using Windows login as user “OPERATOR1”, will the script work? If it does not work, what is the reason?

    Wednesday, October 24, 2012 11:06 PM

Answers

  • From your description, it seems that you have set up the connection to use Windows Authentication. When you use SQL authentication, the user's credentials in Windows does not matter. The user does not even have to be logged into Windos at all - he could come from a Unix box, a Mac or whatever.

    How does your connection string look like?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, October 28, 2012 10:50 AM
  • If you want to use SQL authentication, you should take out "Trusted_connection=yes".

    Then again, it can be questioned whether it's a code it put a user name and password in a script that anyone can read. It may be better to stick to Windows authentication, and grant permissions as needed to people who should have access to the database. But I don't know how sensitive your data is or what security requirements are.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, October 28, 2012 2:51 PM

All replies

  • Question: If I run the application using Windows login as user “OPERATOR1”, will the script work? If it does not work, what is the reason?

    The Windows login doesn't matter for SQL Server authentication.  To connect using an ODBC DSN and SQL authentication, you need to specify a password in your code.  The password used to test the DSN connection isn't saved with the DSN. 


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Olaf HelperMVP Thursday, October 25, 2012 5:02 AM
    Wednesday, October 24, 2012 11:33 PM
  • Hi Dan. Maybe I didn’t explained well. I have the SQL password in the code. I made some tests and my conclusions are as follows:

    1) I call my application and the script runs If: the domain user has administrator rights, or if the domain user doesn’t have administrator rights but is configured/authenticated as a SQL user.

    2) I call my application but the script don't runs if: the domain user doesn’t have administrator rights or is not configured/authenticated as a SQL user. SQL detects that the domain user is not in the administrator group or is not authenticated in the SQL and doesn’t allow the script get the data from SQL.

    Can I say that these sentences are true? Does SQL detect that the domain user is not an administrator or the domain user is not authorized in the SQL and blocks the script access?

    Saturday, October 27, 2012 10:29 PM
  • Hello,

    Dan is correct.

    In rgard to your observations, this depends on what logins and groups have been setup in your instance of SQL Server.

    By default when SQL Server 2008R2 is installed, an account must be set to be a sysadmin. This is probably the account you installed it with. Since SQL Server 2008+, the local Administrators group no longer has access by default, thus it wouldn't matter if you were a local admin, domain admin, etc, if you don't have their windows group or login configured for access - they will be denied. If you only attempt to connect by using SQL Authentication then at NO POINT will the Windows account be used.

    When using SQL Authentication a username and password is supplied in the connection string, regardless of DSN setup as Dan has already pointed out. The login is checked and if it's correct and allowed permissions, it'll connect to the instance.

    -Sean


    Sean Gallardy, MCC | Blog | Twitter

    Sunday, October 28, 2012 12:36 AM
  • From your description, it seems that you have set up the connection to use Windows Authentication. When you use SQL authentication, the user's credentials in Windows does not matter. The user does not even have to be logged into Windos at all - he could come from a Unix box, a Mac or whatever.

    How does your connection string look like?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, October 28, 2012 10:50 AM
  • This is the string:

    DSN=PLANT1;UID=ADMIN;PW=1234;trusted_connection=yes

    The ADMIN is a SQL user

    Sunday, October 28, 2012 11:18 AM
  • If you want to use SQL authentication, you should take out "Trusted_connection=yes".

    Then again, it can be questioned whether it's a code it put a user name and password in a script that anyone can read. It may be better to stick to Windows authentication, and grant permissions as needed to people who should have access to the database. But I don't know how sensitive your data is or what security requirements are.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, October 28, 2012 2:51 PM