Server rejected the connection; Access to the selected database has been denied

Question

From the search I've done this is a problem that people have asked but no one has  posted a valid solution for.

Background:

MS Access front end connecting to SQL Serever 2008 R2 back end.

Use to work fine but recently when I try the login credentials I get the "Server rejected the connection; Access to the selected database has been denied" error, and then it forces me to enter the sa password before it lets me log in.

I do rebuild the db often as I'm passing the work back and forth with another developer in India.

Have tried:

- The user has a log name and password set up on the server with db_reader and db_write roles.

- The  database has been assigned these users also with db_reader and writer roles

Previously that was all it took for it to work but now it seems to require me to first provide the admin name/password.

Any ideas?

Answer 1

We need a bit more information. I think the problem might be, that login L1 has access to database A (has a database user account mapped to the login), but does not access to database B. But login L1 is configured with a default database of database B, to which the login does not have access. Attempts to login, succeed and the login is immediately sent to database B and rejected.

If that makes sense, change the default database of the login, to database A.

I'm not sure what you mean by providing the sa password. I assume you meant that you are then not using the original login, but changing to the sa login. And since the sa login has access to all the databases, and might not have a default database, you avoid the problem.

If this doesn't help, provide a more detailed description of the situation. And search the SQL Server error logs for additional errors.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 2

I thought I replied to this but looks like it didn't save.

Let me know what additional info you need.

Not sure what you mean by L1 and database A etc.. The Access front end could be an Excel workbook for all intents and purposes. It  connects via ODBC to SL Server. Regarding what you say about sa password - yes that is exactly right.

I will have to continue searching when I get more time -- been working 80+ hour weeks.

Answer 3

If Excel is connecting without a password, it must be making a Windows Authentication connection.

I think you need to know more about the logins in SQL Server. Connect with Management Studio and see what type of logins you have. If you need help with Management Studio, start with Lesson 1: Connecting to the Database Engine http://msdn.microsoft.com/en-us/library/e8db82f0-50ed-4531-9209-940006ed34cb

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty