CODEWITHSUNDEEP
sql-serverms-accessauthentication
jpw2007
jpw2007

Reputation: 51

Linking MS Access to SQL Server with Windows Authentication

I have a Microsoft Access database that contains links to tables on a MS SQL Server. These links work fine for me using my Windows Authentication because I have been given full access. I have requested access from our IT department for other users. They would not do that. Instead they provided a new windows user ID and password that has access to the SQL Server we need.

I have tried using SQL Server authentication when creating the link, but it doesn't work. Is there any way to use Windows Authentication but provide this user name and password to the database? That would allow me to send out a copy to all users, giving them access.

I can't have all users logging in to Windows using that same account, so I need a way to specify to MS Access to use the provided user name and password. Any help is appreciated.

Upvotes: 0

Views: 4438

Answers (2)

Albert D. Kallal
Albert D. Kallal

Reputation: 49039

You cannot specify authenticated users when linking to SQL server (it is not possible). You continue as you doing now and simply just link with your logon. Because you using windows Authentication then the USER and the PASSWORD are NOT saved in the linking process. The user’s id and password used to logon to windows will be automatic used. If you could specify other users, then this would default the whole point and purpose of using windows logon. When using windows logon (windows Authentication), then no user name or password is required – the system automatic uses your current logon.

So the IT department cannot issue some windows ID for you to use, because that is NOT the windows ID that users are logging on with (and this you pointed out).

In other words, windows authentication = “please use” the current users ID and logon they used when logging into windows. In fact his is the whole point of using Windows Authenticate in place of SQL server logons. When using windows Authentication, the you do NOT have control over the user name and password used when linking your tables.

As long as you simply link to SQL server when using windows Authentication, then distribution that Access application to all users is thus forcing AUTOMATIC use of windows logon, and does so without intervention on your part.

This setup forces SQL server to use the CURRENT windows user + password.

The IT department thus needs to ensure that authenticated users have use of SQL server. I not sure why they don’t realize this.

If only a specific users are to be allowed, then they need to give those users permissions. Better is to create a security group that has permissions, and then ADD all users to that security group. Once they do this, then again no password or logon will be required to use SQL server – it is automatic and transparent to MS Access. This as noted also means that the users name and password is NOT going to be saved in the Access table links.

It makes no sense for the IT department to issue a windows logon for SQL server. I believe this is mass confusing from your IT department.

You need to clear up if the logon they gave you is a windows logon, or a SQL server logon. It simply not possible to spoof or use OTHER people’s logons + password when using windows Authentication.

edit: If you going to use a SQL logon, then you can most certainly link the tables with the SQL user + password saved and this will eliminate the need for a logon. You can also link tables without the SQL user + password. Then on Access startup you can execute a select query in which will be cached and used for all linked tables. How to do this is outlined here:

Power Tip: Improve the security of database connections

http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx

Upvotes: 1

Marty
Marty

Reputation: 2016

This is the perfect place to use SQL Server authentication, as opposed to Windows authentication. Ask your IT folks to create a new SQL Server login, and give that login the necessary permission in your application's database(s). Then use that credential when you link tables in Access. The team I work on does things this way and it works great.

Aside from simplifying things in general, another advantage you get is that your application will work in environments where a Windows domain credential does not exist.

An interesting article describing the tradeoffs between SQL Server authentication and Windows authentication is here.

Hope it helps. Good luck with your project.

Upvotes: 1

Related Questions