DSN UID and PWD

[rookery]

Does anyone know how you could pass the UID and PWD from a DSN into an Access front end so that it could be used within a connection string?

This would happen obviously at runtime.

Cheers in advance.

 

[MoLaker]

You will have to read the registry to get that info. Look under HKEY_LOCAL_MACHINE > SOFTWARE > ODBC.

Here's an example of how to read the registry.

http://www.arcatapet.net/vbregget.cfm

Keep in mind, you must have permissions to view the registry.

 

[rookery]

Thanks mate. Will expolre this further at work tomorrow!

 

[jsteph]

I'm not sure how you're using the dsn, but you could store it in a table, which is my preferred method. Reading the registry assumes you know the name of the dsn and that the password is stored there--which means the Access code must be hardcoded to know the dsn name.

If you know the name of the dsn, then why not store the dsn and entire connect string in a table? The table can be secured, the code can be secured, so you read the table and get the entire connect string. Then you can use it to link tables and open connections, without hardcoding anything. If the server changes, or password or anything, then change the table entry without changing any code.
--Jim

 

[jsteph]

...if it wasn't clear--I mean store it in an Access table.

 

[rookery]

Thanks for your reply jsteph. I didnt really want to store the info in a table due to security concerns. I know you can lock the file down somewhat but there are workarounds that allow access.

That is why I wanted to be able to read the info direct from SQL/registry.

 

[MoLaker]

I guess I'm getting confused. Does the DSN have embedded UID and Password? If so, then what's the point? You connect via the DSN without UID or Password info in your connection string because it is already in the DSN. Those are only needed if they're not contained in the DSN and in that case you won't be able to extract them from the DSN anyway.

From the following quote

That is why I wanted to be able to read the info direct from SQL/registry

I get the impression you are trying to get the SQL UID and Password from SQL Server, not the DSN. If that's what you are trying to do, good luck.

 

[rookery]

MoLaker in my experience even though the UID / PWD are included within the DSN you still have to specify them in the connection string.

It may be that I'm doing something wrong but without explicitly referencing I get prompted to supply them when linking to the SQL tables.

That is why I thought I might be able to get it from somewhere else - either the registry or SQL. I have no idea how easy it would be to do this though I doubt it would be a walk in the park.

 

[MoLaker]

Hmmm. Whether or not the password and ID is included in the DSN configuration is an option choice made when the DSN is created. It's my experience that if the password and ID are included in a DSN configuration, you are NOT prompted for them. If you are prompted for the password and ID, then I suggest they are NOT embedded and therefore not available in the registry to be read.

As for getting a password and ID from SQL Server, the inability to do so is one of SQL Server's security advantages. If someone has a method to extract this information from wherever, I'm sure Microsoft would be very interested.

 

[rookery]

Where is the option box that you refer to in the DSN? If it's the "Connect to SQL Server to obtain default settings.." this isnt the one as I have that checked already.

BTW it's a system DSN that I'm using.

 

[SQLSister]

ODBC;Description=Connects to dev01Server;DRIVER=SQL Server;SERVER=Test;UID=myusername;DATABASE=dsi;Trusted_Connection=Yes

Is this something like what you want? It passes the username but not the password as it is using a trusted connection.

Questions about posting. See faq183-874

 

[MoLaker]

You made me install SQL Server. I was trying to run from memory and that is no longer a good idea at my age. Seems what my memory was telling me was really using NT authentication instead of SQL Server authentication.

So, back to your problem - I'd say you're out of luck as far as grabbing the SQL Server password and ID. But, I think it is possible to use a DSN w/o prompting or embedding a password and ID if the DSN is set to NT Authentication. The necessary NT users/groups must be included in SQL Server security logins and permissions set accordingly.

Here's sample code I used to connect to Northwind using NT authentication. It did not prompt me for a password and ID.

DoCmd.TransferDatabase acLink, "ODBC Database",_
    "ODBC;DSN=localServer;DATABASE=northwind;",_
    acTable, "Employees", "Employees", , True

SQLSister's solution works, too. But, I believe the DSN must still be set to NT authentication and NT user/groups and permissions set in SQL Server.

I don't know if this is a possible solution for you, but it's the only way I know how to do it.

 

[rookery]

Sorry Mo didnt mean for you to go to all that trouble!

Yes it DOESNT prompt you if you're using NT but unfortunately for me, I'm using SQL security.

Thanks for your troubles mate anyways.

PS. Thanks to Sis too.