Connection errors (1 Viewer)

[rastrix]

This post may contain too much information, but more is better than not enough, and the past week has seen some 'fun times' trying to get this going...

We run a network with 2 domains, A and B, B trusts A for login privleges, etc. A is a windows 2003 domain and B is a windows NT domain. Domain B is on a different network from Domain A. routing from one network to the other works, Everything worked fine.

SQL Server is housed on a Windows 2000 server machine, and hosts several network shares.

Users had used a windows 2000 terminal Server (on domain A) to open an access database, linked to the SQL server, and accessable from a network share on the SQL server. This server died.
We re-used an under-utilized windows 2003 Server (TS enabled) joined to domain B. Configured the ODBC connection as per configuration on previous machine. (thankfully saved) Users would log in onto domain A (trusted) as this was the previous setup.

I recieve these errors:

connection failed
SQLState: 28000
SQL Server Error: 18452
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)', Reason: Not associated with a trusted SQL Server Connection

connection failed
SQLState: 28000
SQL Server Error: 18452
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'domain\User', Reason: Not associated with a trusted SQL Server Connection

Initally I thought that the servers themselves were not authenticating to the domain(s) properly. The IP addressing scheme for the network where domain B's Primary server was located has been recently changed, and for a while users from either domain could not log on to either the 'new' machine or the machine housing the SQL server(admins could). That was corrected quickly and easily, but the problem persists. Administrative accounts for either domain and local admin accounts(on SQL machine) are able to access the database without issue. User accounts cannot. This is true both on the local machine, and from the 'new' TS computer.

To me, It almost seems line the SQL server itself is not authenticating to the domain(s) properly. I tried adding a new group to the SQL logins referencing an existing group for settings and using a newly created domain group and recieved this error:

error 14501: Windows NT user or group 'domain\groupname' not found.
check the name again

I receve the same error whether I try to add a group from domain A or domain B.

I have good techincal knowledge, but very little in the area of SQL server any help or hints would be appreciated.

 

[SQL_Hell]

Hi there

There a few things I dont quite understand in your post:

Is this access database set up as a linked server on sql server?

Are all the autentication problems related to this linked server?

When you admins can log on and users cant, are these all domain login accounts? always 3 machines involved?

Why was the 2003 replacement box added to domain B rather than domain A?

 

[rastrix]

I'm not well versed in the terminology for SQL server, I'm just trying to make work what had previously been working.

The Access database is a custom program for our company that pulls its data from the SQL server, There is an ODBC connection to the SQL server that is used.

It seems that all of the authentication problems are related to the SQL server. I just learned about another sql-based application that runs off that same server that is having the same issue. Administrative accounts can use it, user accounts cannot. These users could previously access the database.

They are domain administrator accounts and the user accounts are domain user accounts. I'm trying to decypher how the permissions are assigned, by the looks of it the local admin group has master access, and the domain admins are part of the local admin group on the SQL server. Some of the domain B groups have been added explicitly with access to certain databases. These groups are still present on domain B. Then there are a few local users and local groups with access.

The 2003 server was added on domain B because it runs a third-party software that synchronises our user accounts from another system to the AD, it needs to be a member of domain B to accomplish that, that same software runs on another machine for domain A.

 

[SQL_Hell]

Ok, yeah the terminolgy can lead to misinterpretations on my part, hence all the questions.

I think I understand now, the data is actually in SQL server and the access database is just for forms and reports, is this correct?

If this is correct, how did you transfer over the database to the new server? backup and restore?

 

[oumahexi]

We experienced something similar a few years back, luckily, I wrote down what we did. You need admin rights to do this, but what we did was to delete the users and re-create them using the exact same password. This fixed the problem for us.

Another thing to look for is, has someone added/removed anything from any of the SQL tables, because if they have you may need to refresh your ODBC links to the tables/views used by your FE.

Hope this helps

 

[rastrix]

Correct, the Access MDB does all the pretty stuff..

The SQL server, and Access MDB were both 'physically' located on the SQL server machine. The mdb was/is accessed through a network share on that machine. It was set up this way as part of a centralized backup strategy, all parts of one thing get backed up together. No changes have been made to the SQL server configuration.

The 'Client' machine (as it was), was TS enabled and had access installed, and the ODBC connection for the SQL server. The new 'Client' machine is essentially configured the same way. (win2k vs win2k3, also domain B member with trusted A, rather than domain A member)

The SQL server machine itself was configured with some static mappings for 'domain controller B' and these were changed to reflect the changes made when the Domain controller's IP's were changed. Comparing dates, it appears that it continued to function for several days after this change was made, but I am beginning to wonder if it died that day and people 'think' it still worked for a few more.

What bothers me is user accounts and admin accounts from both domains are able to authenticate on both machines without issue, the problem appears to only affect SQL. (SQL server machine is also a member of domain B)

 

[SQL_Hell]

If nothings changed on the SQL server, then there must be something wrong with the domain authentication.

Please run the following on your server

SELECT * FROM SYS.SYSLOGINS --IF SQL 2005
 
SELECT * FROM SYSLOGINS --IF SQL 2000

Check whether the logins in sql server match, the accounts that the users are logging onto the domain with.
Are your users accounts in an AD group?

Now also check the user accounts into your database

USE YOURDATABSENAME
 
SELECT * FROM SYSUSERS

Check there are user accounts for all the user logins.