ODBC Connect - username / password help

[peter_turner]

Hello Everyone,

I have a problem when connecting to a SQL database and trying to retrive data with the ODBC CONNECT command.

On my development laptop (Vista 32bit) i can use the following command with no problem:
ODBC CONNECT TO my_database

But on my server (2003 std 64bit) the command fails as it requires additional username and password information. I have to edit the project and re-make the ODBC connection command, which then looks like:
ODBC CONNECT TO my_database (XUserId is xxxxxyyyyzzz, XPassword is xxxxyyyyzzzz);

In both examples i make a system DSN ODBC connection in exactly the same way with the same username/password access to the database and the test works fine. But on the server it requires the authentication again, as if the DSN is'nt storing the details correctly.

1,
Is there a way to stop SQL2005 from requiring the authentication details again?
As the Xuser and XPassword encrypted keys change on each server, even when the database username and password stay the same, it means i would need to manually edit each customers project file.

2,
If the authentication is unavoidable, is there an application etc that i can use to get the Xuser and Xpassword parameters?, which i can then pass to my project when it loads.

Any thoughts / things to try would be greatly appreciated,
Peter.

[peter_turner · Accepted solution]

Hello Everyone,

To update and close my question...

1, If the SQL server has a specific Username and Password, it is possible for QV (and other ODBC apps) to generate a different XUser and XPass key sequence each time.
Even though the hash key shown is different each time you make the ODBC connection in the script editor, they all still evaluate to the same basic username and password for the SQL.

2, It is possible to avoid authentication, by setting your windows ODBC connection to use windows/user authentication, instead of SQL authentication.
The connection is then linked to your windows usernames, so its a good idea to have your QVS service's running with a proper admin user to get access to the SQL database.

[blaise]

I havn't had any problems with moving the database connection string between environments. Ive done it about 100 times and it hasn't failed once. But if you have some kind of unusual setup (i dont know) try using OleDB instead.

Of course the Xuser and Xpassword is different each time you set up a connection (to the same server and usr/pwd). If they were identical it would be to hard to guess the algoritm :). So the answer to the question if there is a application that can read the xuser and xpassword is NO.

Of course you can turn off authentification, but i guess the DBA might get a bit confused about your security approach.