OSQL and ODBC DSN
-
I am trying to execute the OSQL statement below with a DSN connection instead of providing the SQL Server login userid and password in clear text.
osql -S test_server -d test_db -e -D test_DSN -n -b -i D:\ETL_scripts\test\spend\lm_edw_table_archive\edw_table_archive.sql
I have defined the test_DSN with ODBC data sources Administrative tool and I tested the connection successfully. The SQL Server login I used for the DSN is test_user and the password is (say) test_pwd.
When I try to execute the osql command with the DSN name from the command prompt I get an error message saying "Login failed for user 'Viji'". 'Viji' is the network login that I have used to login to the machine from where I am trying to execute the osql command.
My question is 1. Why is trying to use the "Viji" login when it should use the "test_user" SQL Server login from the DSN?
2. Why is the osql not authenticating my osql command using the values I have saved in the DSN?
Thanks in advance for all responses to my queries.
Viji
-
I do not want to connect with a trusted connection (-E option). I want to use the test_user SQL Server login and test_pwd SQL password. Thanks for responding.
-
See osql in BOL:
If neither the -U or -P options are used, SQL Server 2000 attempts to connect using Windows Authentication Mode. Authentication is based on the Microsoft Windows NT® account of the user running osql.
-
When I add the -U test_user to the osql command, I get an error message saying "Login failed for user test_user". I do not understand why it is not able to read the password from the DSN that I created.
-
If you are using the standard ODBC driver for SQL Server, SQLSRV32.DLL, it does not save the password within the DSN. Even if you go into your DSN from the Data Source administrator, it requires you to enter the password before it lets you navigate past the username/password screen. The DSN actually tests your authentication info prior to allowing you to go any farther in the ODBC configuration. The only time the SQL Server ODBC driver lets you go past the username/password screen without inputing the password is when the password for your user is blank.
-
If that is the case, then how do applications that use DSN connections connect to a ODBC datasource without entering a SQL Server password?
Also, is there then no way to write the OSQL command such that the password is not displayed as plain text? I am saving the OSQL command in a bat file that will run every day from a scheduler program and the console output will be written to a log file. When I type the password as plain text into the bat file, the password will go into every log file that is created because it is displayed as plain text in the command window. Please advise.
-
Why can't you use integrated security? Wouldn't this address all your concerns?
--------------------
Colt 45 - the original point and click interface
-
I have seen application that use ODBC gather the username/password during runtime and pass them into the connect string along with the DSN.
You may want to explore using the trusted connection. Can you limit user access on the scheduler box? Are you just selecting data or updateing/inserting?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply