Login to Sql Server (Access 2000)

Topic

New Reply

My Access database uses several linked tables from a Sql Server 7 database. The first time I try to open a form or report, I get a Sql Server password dialog, and have to enter the password. Thereafter (during that Access session), the password “holds”, and I don’t have to enter it again.

Any ideas on how to force this password in, so users don’t have to enter it? An auto-exec macro, some vba code, etc.?

Thanks for any help on this issue.

Reply | Quote

Replies

If you hard code it into Access anywhere, you’ve just laid your SQL Server database wide open. However, there’s no reason you can’t use a UDL to link to the database, and you could store the login and password in that.

Reply | Quote

Are you using Windows security in SQL, or are you using mixed security and logging into SQL with a SQL only password? (Hopefully not sa!) We run a number of systems using ODBC links to SQL, and in general there is no login prompt required as long as you are using Windows security. The downside is that you do need to implement permissions via Users and Roles to make that work well. On the other hand SQL Security is more robust than Access. Speaking of which, there’s a great new book out that will get your attention if you are concerned about the possibility of hacking SQL Server. The title is “Hacking Windows 2000 Exposed” and it has an entire chapter on the ways people might try to exploit weaknesses in SQL and Win2000.

Reply | Quote

Thanks for the quick reply.

I am using SQL Server security. We have a user set up just for this application, with the proper roles/permissions. When I open the database, no password is asked for. However, when I open a form or report which is using one of the linked tables, the login dialog appears. Since we’re using a dsn, the proper username appears (the one attached to that dsn), and I need to enter the password. This only happens once per Access session.

What I’m looking for is a way to do this first login programatically so the user doesn’t have to do it. I am also using ADO for certain pieces of the application, where I need to access the tables directly, without using a form. I wonder…if I open the public connection object at the beginning (autoexec macro running code) maybe that will work? Any ideas? Also, Charlotte mentioned using a udl file instead of hard-coding my connection string properties (thanks, Charlotte). Don’t know how to do that yet, but will research msdn.

Reply | Quote