Active Directory Authentication SQL 2000 SP4

[229257]

Hi,

We are currently experiencing a problem with SQL 2000 SP4 whereby access is denied to an Active Directory group but not an AD user.

We gave the AD group an SQL login and granted the relevant permissions to the database. When we try to create an ODBC connection as a user of this group the connection fails with a server rejected the connection message.

When we grant an AD user (who is a member of this group) the same permissions it works no problem.

We have checked the following:
- The user has access to default DB
- The user is a member of the AD group
- We tried this on a SQL2000 SP2 server and it worked
- Mixed mode authentication is enabled
- TCP/IP and named pipes are enabled
- Granting the same permissions to an AD user works
- Granted permissions through SQL statements and Enterprise Manager

Has anyone experienced this problem? Does anyone have any ideas on how to resolve this?

This is a newly built server and this has never worked on this box.

One work round is to give each user in the group an SQL login but this will dramatically increase the administration required.

Many thanks,
229257

 

[mrdenny]

What's the exact error that the user gets?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[229257]

Hi There!

The exact error message is

Connection failed:
SQLState: '08004'
SQL Server Error: 4062
Server rejected the connection; Access to the selected database has been denied

I have tried some other things since my first post without any joy:

- Gave this AD group full permissions on a test DB and still didn't work
- Gave this AD group server role 'System Administrators' and this worked (this is not a possible solution it was just a test)

Thanks
229257

 

[mrdenny]

What default database is the AD Group setup with? Does the AD Group have access to the database? Try changing the default database to master.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[229257]

Hi,

I have tried setting the default database to both 'master' and the test db which I have granted the group access to, unfortunately this doesn't work.

The frustrating thing with this is if I add the AD user everything works as expected.

Thanks
229257

 

[mrdenny]

It sounds like something is not set correctly with the domain group. Are you trying to log into the database with a third party / home built app, or the SQL Tools?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[229257]

Hi,

I am using a group that has been working on a SQL Server for the last couple of years. It works on the old box but not on the new box.

As a test I am trying to setup an ODBC connection to a database on the new server (this is where i am getting the error), I have tried making the default database 'master' and also the 'user' database I have added but they both give an error. The next test once I get a connection is to download some data into Excel.

Just had a thought - I have only tested the ODBC connection from one workstation, I will try another tomorrow to rule out a problem the workstation that is trying to connect to the new SQL server. I will try this tomorrow and I'll post my results.

Thanks,
229257

 

[229257]

I have tried creating an ODBC connection on a new workstation but the problem still occurs.

 

[mrdenny]

My best guess without looking at the machine is that there is another domain group which has been denied access to the database somewhere and that deny is overwriting the grant within this group. Are there any other domain groups which the user is a member of which have logins defined on the server?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mrdenny]

My best guess without looking at the machine is that there is another domain group which has been denied access to the database somewhere and that deny is overwriting the grant within this group. Are there any other domain groups which the user is a member of which have logins defined on the server?

What happens when they try to log in with the SQL client tools?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com