ODBC connection failed....HELP!

RichO

RichO

Registered Yoozer
Local time
Today, 01:59
Joined
Jan 14, 2004
Messages
1,036
I have my share of experience with MS Access but I know almost nothing about SQL server.

As of this morning, at my office Access cannot connect to SQL server. Everything was fine with it for the last 12 years with no signs of trouble. At first I thought it was a network problem but even on the computer where SQL server is installed I can't connect to it from Access.

I went into Enterprise Manager and was able to open the tables from there, so it appears that the information is intact, but the connection fails. Also when I go to Admim Tools in the control panel, I cannot connect to it there either.

SQL server was installed and set up by a former employee many years ago and technically I am the IT guy here now, but I am lost.

Also, when I try to access the back end by linking tables I get error 18456. From what I find this is what it means:

Error: 18456, Severity: 14, State: 12 means that the authentication is successful, but the server access fails due to permission.

Nothing has been changed regarding this machine. Nobody has even been using the computer containing SQL server so whatever happened wasn't because of that.

Any help would be appreciated. Thanks
 
Last edited:
What version of SQL Server? Version of Desktop OS. Was there an update to IE or other MS updates lately?
Do you have rights to the Enterprise Manager to evaluate rights?

Once, I had a site with SQL Server maintained by a network professional. He ended up adding a new update to MS Outlook on the same cluster. Well, 36 hours later (spending 12 hours on a MS paid service call) it turned out that a DLL to Outlook did in fact change the SQL Server security setings. B.T.W. all backups previous to that were useless. Lucky, I did a full backup on the flash drive.
This probably isn't your case, however - it just indicates that an external upgrade can change things.

Other questions: Do you have an ODBC driver on each workstation?
Or do you use SQL Server Native Client 11.0 (free download)
Do you set up the ODBC or use an DNS-Less connection?

From your description, my guess is that it is something on SQL or the network.
e.g. what is the PORT numbe on SQL. Is the PORT specifically listed on your ODBC box? I had a situation on a government site where desktops used the default port on the workstation ODBC setting. One day, nobody could log in. I manually added the default port on the ODBC configuration.... it worked. I have no idea why that happened or why it made a difference.

Are you using Integrated (network) or SQL Server security.
 
Wish I could offer more help, I have some very important appointments to make. Just wanted to suggest this for your consideration.

The mention of 12 years ago makes me think you are using SQL Server 2000. It is possible some network upgrade may have affected it.

There is a point of using a MS Paid Support call. It has been a while since I have done that. It might be around $200 per incident.
For me on the last one, they stayed on for over 24 hours. They even backed out the cost as it was something they caused with an upgrade.
If you have an older version, that probably won't be the case.

But, bottom line, if you are the IT person that has all eyes on you, there is a point of getting it working. Then give the bill to management as a "cost of doing business". Expecially if they have been saving money by not updating versions.

I have to come in and work Sunday. If you post more information, I will try to help, and hopefully so will others.

Let us know if you figure it out. Best of luck.
 
SQL Server 2000, Windows 2000, like I said, installed many years ago.

No updates done to anything. It worked at closing time yesterday and this morning it did not and nobody else could have been here. The computer with SQL server has been logged off all week. Nobody uses that actual computer as their workstation.

Other questions: Do you have an ODBC driver on each workstation?
Or do you use SQL Server Native Client 11.0 (free download)
Do you set up the ODBC or use an DNS-Less connection?

From your description, my guess is that it is something on SQL or the network.
e.g. what is the PORT number on SQL. Is the PORT specifically listed on your ODBC box?

Are you using Integrated (network) or SQL Server security.
Click to expand...

I would not know the answer to this. Each computer on the network (only 4) has the connection name listed under System DSN in Admin Tools. I don't believe there is any native client software.

At first I thought it was a network issue but I can't even connect to SQL server on the actual machine hosting it.

That's about all I know about the set up.

Thanks for your help.
 
Let me ask this:
You can log onto the actual SQL Server machine hosting SQL Server 2000 as Admin
You start up SQL Server Management Studio (success or not?)
Start up Component Services:
Is SQL Server (Name of your DB) started?
Is the DHCP Client started?

Ther is more to check, this should be a good start.


Do you have the IP address for the SQL Server? Can you ping it?
1) You can use telnet command in command prompt to test port connectivity. Telnet IPAddress PortNo
2) Check windows firewall exclusion list for port no.
3) You can also use tool , PortQuery to check port connectivity. ​
http://www.microsoft.com/en-us/download/details.aspx?id=24009
 
I will have to check into this tomorrow when I am back at work. I will let you know what I find out. Thanks
 
Check the permissions settings for the users and the objects on the server. Maybe they got corrupted somehow.
 
Check the permissions settings for the users and the objects on the server. Maybe they got corrupted somehow.
Click to expand...
By server do you mean the PC containing SQL server? That computer can be successfully accessed through the network by all other computers.

The computer that contains the data and SQL server can't even connect to SQL server through MC Access.
 
I don't kow what 2000 Enterprise Maanager looks like but I guess it s similar to Management Studio on later versions.

Under the database there would be a security folder that lists users/groups. Opening that shows the persmissions for that user.

Are you using Windows authentication or SQL Server Authentication?
 
OK I will check into that tomorrow when I am at work.

As for Windows authentication or SQL Server authentication, I didn't set this up so I have no idea. How would I find this out?

Thanks
 
Once again I only know SQL Server Managerment Studio.

In SSMS there is a Security folder at the top level that sets the security for each user/group login. The authentication type is set there.

However since your error message says they passed authentication it probably isn't an issue. Sounds more like permissions have gone wrong on the users or tables.

Or something hideous like Dave described.
 
Maybe a long shot but note that it is possile to change permissions on SQL Server in very devious ways. If security has not been set up properly a user could send commands to change these settings.

Furthermore, if the input has not been secured against SQL injection attacks it is possible for a user to bypass security and inject these commands by specially crafted data entry.
 
OK I did check permissions for the users within Enterprise Manager and they all set to be set normally.

Because time is of the essence here I did have to reach out to a computer tech company to see if they can find the problem. This seems to be way over my head at this point.

Thanks for all your help.
 
I too have forgotten what the old 2000 version looks like.
Here is a small snapshot (with details removed) of the MSSSMS view from 2008 version.
From my fuzzy recollection, it should be very close to the same.

I keep one login as SQL Server Security - that means it uses a username and password. It comes in handy for testing. Did your ODBC require a user name and password? That would be good information to know. The ODBC setup on the user workstation has two choices that should be helpful to answer the Network Security (sometimes called Integrated Security) or SQL Server Security (user name, password).

If you create a user with DataRead and DataWrite permissions for a DB, it can be used to test ODBC connections.
 

Attachments

  • MSSMS Quick View.png
    MSSMS Quick View.png
    41.3 KB · Views: 330
The only connection that was set up had a user name of "sa" and no password. I think that's a default login isn't it?

Being that I have never used this software I will have to try to figure out now to create a new user and see if I can connect that way.

There is only one user besides myself in this office so there is no way that anyone did anything to bypass security. Actually I was the last one here the last day it worked and the first one here the next day when it failed. Happened overnight.
 
RichO said:
As for Windows authentication or SQL Server authentication, I didn't set this up so I have no idea. How would I find this out?
Click to expand...

See attached (right-clicked on local and chose properties) , since I appear to be the only one with an antique version of 2000 running. :p
 

Attachments

  • SQL Setting.jpg
    SQL Setting.jpg
    94.6 KB · Views: 171
OK, it is using SQL server authentication.

This is what I found out. There are 3 users under this database.

users.jpg


The "dbo" owner with the "sa" login is the one that fails. I was able to connect to the "scottb" user and I even changed permissions for a few of the tables...

permissions.jpg


But when I connect to the "scottb" user through MS Access to try to link to those tables, they do not appear in the list.

From what I read, you cannot change ownership of the database but how can I access all of the tables from MS Access through the scottb user?

If I can do this, that is the solution to my problem.
 
You must log in or register to reply here.

Similar threads

H
Efficient Deployment of MS Access Applications with MS SQL Server
Replies
10
Views
760
Isaac
Isaac
A
MS ACCESS connect to SQL server BE: Authentication Prompt every time I attempt to
Replies
4
Views
410
GPGeorge
GPGeorge
Charax
Error Installing SSMA
Replies
16
Views
456
Pat Hartman
P
M
Importing PowerQuery results into SQL Server
Replies
3
Views
244
arnelgp
arnelgp
S
Solved Managing SQL Server Logins in MS Access Frontend
Replies
19
Views
1,236
AHeyne
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom