January 29, 2016 at 3:04 pm
Have a linked table Access DB that connects to a SQL server instance running on a server.
Worked until January 1st for the last 9 years.
Now in event viewer of the Server - Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
Using a DSN file
[ODBC]
DRIVER=SQL Server
UID=username
PWD=password
DATABASE=db2
WSID=workstation
APP=Microsoft Data Access Components
SERVER=192.168.1.200\data
Trusted_Connection=Yes
Running Access
It comes back and say ODBC Connnection 192.168.1.200/data failed
WHAT THE HECK CAN I DO?
I can log into the SSMS and do anything to the database. I made NT AUTHORITY/SYSTEM sysadmin and defaulted the database to db2.
It just stopped for no reason!
January 29, 2016 at 5:58 pm
Let me start out by saying that I'm nowhere near being an Access expert.
First, I've heard of Access linked table "just breaking" for no apparent reason. Can you break the linked table and then re-add it? I've seen this do the trick before, but don't know the root cause.
Next, what service packs and security updates have been installed recently? There may be nothing here, but if you've installed anything lately, it would be worth investigating.
Lastly, be very careful when granting the sysadmin role to logins. You want to keep those under tight control.
I hope this helps.
January 30, 2016 at 6:52 am
What changed on the system? Something has to be different. Determine what that is and you may have your solution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 30, 2016 at 7:09 am
The cause of the problem is that the database named "db2" is either no longer on that instance, or can't be opened or the login no longer has permission to that database. You'll need to connect to the SQL instance and see what's happened.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2016 at 8:27 am
As far as updates or service packs, there have not been many. This db has been used in an archival mode only for the last 2 years. We look at it only for information, we don't add or change anything.
As far as sysadmin, dbadmin, because we don't do anything but look at it, I will do anything to get access.
As far as re-linking tables, I don't see how. I can't connect.
I can get into SQL Service Management Console and do anything, that is why I asked the question here, how do I make this work again?
January 30, 2016 at 8:59 am
The login that the access app is using can no longer connect to the DB db2. Either:
- The DB is no longer there
- The DB cannot be opened
- The login no longer has permission to access the DB.
Work out which of those it is, then you can figure out how to fix it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2016 at 9:08 am
- The DB is no longer there
In SQL Service Management - I can log into the DB and see it and see permissions and users.
- The DB cannot be opened
I can query the DB in SSMS and see tables and all kinds of stuff.
- The login no longer has permission to access the DB.
In SSMS, I see the account I am using and it has public, owner, and sysadmin rights to the db.
So, those 3 things are answered, the problem that NT Authority/System cannot open the database, even though it is listed in Logins gives me the error I said in Event Viewer.
What do I do now?
January 30, 2016 at 9:23 am
cortech (1/29/2016)
Using a DSN file[ODBC]
DRIVER=SQL Server
UID=username
PWD=password
DATABASE=db2
WSID=workstation
APP=Microsoft Data Access Components
SERVER=192.168.1.200\data
Trusted_Connection=Yes
The DSN settings makes no sense. If you are providing a username and password then why are you also saying it is a Trusted Connection? The error, which includes in the message reference to a Windows account, explains that the username and password in the DSN are being ignored.
A couple questions:
1. What or whom is trying to use the Access database when you get this error? Is it a Windows service (something unattended) or an interactive user (i.e. you or a coworker)?
2. Is the Access database being opened on the same machine where the database instance exists or is Access being opened on a different machine?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 30, 2016 at 9:44 am
A couple questions:
1. What or whom is trying to use the Access database when you get this error? Is it a Windows service (something unattended) or an interactive user (i.e. you or a coworker)?
It is just any computer on the network. From a shared file.
2. Is the Access database being opened on the same machine where the database instance exists or is Access being opened on a different machine? No Access is not installed on the server. It is on a computer on the network from a shared file.
January 30, 2016 at 10:04 am
cortech (1/30/2016)
A couple questions:1. What or whom is trying to use the Access database when you get this error? Is it a Windows service (something unattended) or an interactive user (i.e. you or a coworker)?
It is just any computer on the network. From a shared file.
Still need to know what or who, not just from where. Is it a person opening Access directly, e.g. Start>Programs>Microsoft Access then File>Open to open the database file? Or are the users simply finding the Access database file on the network and double-clicking it? Or something else? Need to understand the stack so I know what the intended security context is of the connection to the SQL Server.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 30, 2016 at 11:50 am
They double click the db2 file and then it opens in Access. Forms then run.
January 30, 2016 at 12:44 pm
cortech (1/30/2016)
They double click the db2 file and then it opens in Access. Forms then run.
Trying non-intrusive changes first...keeping modifying Access (what Ed suggested) as a last resort...
From what I have heard so far it sounds like the authentication attempt from the client does not contain a valid domain token. Has the user experiencing the issue changed their domain password lately or have any changes been made to the domain controller, e.g. has it been rebooted or patched lately? In any case, have the user log off their workstation and then log back on to refresh their domain token. Or better yet, just have them reboot their workstation in an attempt to fix the issue.
If that does not resolve it the next thing I might try would be to attempt to connect to the SQL Server from a client-tool other than Access. Go to the Windows ODBC control panel and test out a new connection to the SQL Server. It is possible this is an Access issue.
If the test from the ODBC control panel works then it would start looking like an Access issue. At this point I would delete and recreate the Linked Table in Access.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 30, 2016 at 1:01 pm
Ok, new development.
I opened 1433 in my SQL firewall - I created a SQL ODBC connection and it worked!!
I then ran Linked Tables Manager in Access and entered my new login usernames and passwords and it worked! All tables refreshed successfully!!
HOWEVER, it seems like it loses the connection. If I close and reopen the database, it breaks. I go to refresh linked table again, it say password is wrong. I retype it and it works!!
Where is that information held? Anyone know?
January 30, 2016 at 1:10 pm
cortech (1/30/2016)
Ok, new development.I opened 1433 in my SQL firewall - I created a SQL ODBC connection and it worked!!
I then ran Linked Tables Manager in Access and entered my new login usernames and passwords and it worked! All tables refreshed successfully!!
HOWEVER, it seems like it loses the connection. If I close and reopen the database, it breaks. I go to refresh linked table again, it say password is wrong. I retype it and it works!!
Where is that information held? Anyone know?
In the DSN...but your DSN as shown in the OP is set to use a Trusted Connection so a username or password should not be required.
Since both Trusted=Yes and User/Pass were provided in your DSN, try blanking out the username and password to make sure it is using Trusted.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 30, 2016 at 1:17 pm
I removed Trusted Connection from DSN because I could not link tables using Trusted Connection check box.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply