Solved

MS SQL Server 2000 Login - Connection failed

Posted on 2010-08-15
29
514 Views
Last Modified: 2013-11-05
I have had to re-install MS SQL Server 2000 on a Windows 2000 Server.  Once installed and service packs 3 and 4 were installed, I created a new database and restored the original database using the latest backup that was made before the re-install of SQL Server 2000.

Once all of the above was done, I logged into a separate  W/xp workstation that I use for my development work and tried to create an ODBC from that workstation to the file server (W/2000 Server) that now contains SQL Server 2000.  The ODBC was created using the "SQL Server" driver from the drop-down list.  I typed in a name for the data source (DSN-ANTS) and the name of the server (AIMSERVER).  I selected Windows NT authentication because that is how SQL Server was installed.  After clicking the OK button, the following error appears:

Microsoft SQL Server Login
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.
OK

(See attached image of error)

I have tried everything I can think of to get around this but nothing has worked.  I want to continue using Windows Authentication as that is what the production environment uses, and once I get this working again, I will need to transfer a VB 6 application with embedded Crystal Reports over to that production environment.

Thank you for the help in resolving this issue.


Error-message.JPG
0
Comment
Question by:EYoung
  • 11
  • 10
  • 7
  • +1
29 Comments
 
LVL 7

Expert Comment

by:macentrap
Comment Utility
have you added the NT user into security Login through enterprise Manager and given database access
0
 
LVL 7

Expert Comment

by:macentrap
Comment Utility
login information is stored in master database
0
 
LVL 7

Expert Comment

by:macentrap
Comment Utility
You have to be builtin Administrator to assign rights . To give
access to users open Enterprise Manager, connect to your instance, go to
Security folder, login, right-click, select New Login.

Tab <Database Access> give access to desired daatabases.
0
 
LVL 4

Expert Comment

by:Sarika30
Comment Utility
You need to set the authentication to mixed mode.

1. Start Enterprise Manager.
2. Expand Microsoft SQL Servers, and then                                expand SQL Server Group.
3. Right-click the server that you want to change to SQL Server and Windows authentication, and then click                                Properties.
4. In the SQL Server Properties dialog                                box, click the Security tab, click SQL Server and                                Windows, and then click OK.
5. When you are prompted to re-start the SQL Server                                service, click Yes.

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
please read this article to understand and troubleshoot that kind of problems:
http://www.experts-exchange.com/A_1881-Connect-to-your-SQL-Server.html
http://www.experts-exchange.com/A_1881.html
0
 
LVL 7

Author Comment

by:EYoung
Comment Utility
macentrap:  OK I gave access to the desired database, but the ODBC did not work until I followed Sarika30's directions about setting the authentication to mixed mode.  However, Sarika30, I would prefer to not use mixed mode as the production environment does not use that.  They use Windows Authentication.  Also, when I use mixed mode, the VB6 code does not work.
0
 
LVL 4

Expert Comment

by:Sarika30
Comment Utility
Are all your  machines in different domain or same domain.
0
 
LVL 4

Expert Comment

by:Sarika30
Comment Utility
Your user had to be set up to have permissions in the server's domain even though they had access on the SQL Server database.
0
 
LVL 7

Author Comment

by:EYoung
Comment Utility
Sarika30:  All machines are in same domain and I believe have permission in the server's domain, i.e. my workstation can connect to the server and see the folders and files.

It is about midnight here and I need to get some sleep.  Will return tomorrow.  Thank you for the help.
0
 
LVL 7

Expert Comment

by:macentrap
Comment Utility
are you trying to use SQL authenttication in Query but the sever is just using NT authentication

if so you can try this..

http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/a7404cc6-f368-4331-8462-c58848634d9a
0
 
LVL 4

Accepted Solution

by:
Sarika30 earned 200 total points
Comment Utility
I read it somewhere:


This is for WINDOWS AUTHENTICATION:
I received this error when connecting to the SQL Server remotely via  my application. After researching, I found that you had to both enable  trusted authentication on my SQL Server machine and grant the User Right  “Enable computer and user accounts to be trusted for delegation” (Group  Policy…under Local Policies\User Rights Assignment) to the Windows  account you are using. I just granted it for the Administrators group.
For the SQL Server machine, I set the property in Active Directory  (check the box labeled “Trust computer for delegation” in the computer’s  properties).
Make sure you for the group policy changes (gpupdate /force).
0
 
LVL 7

Author Comment

by:EYoung
Comment Utility
Sarika30:  Where in the server's Active Directory tool do I make those changes?  Thanks
0
 
LVL 7

Author Comment

by:EYoung
Comment Utility
I have figured out the Group Policy part but have not figured out how to: "enable  trusted authentication on my SQL Server machine".

Put another way, how do I:

"For the SQL Server machine, I set the property in Active Directory  (check the box labeled “Trust computer for delegation” in the computer’s  properties)."

Thanks
0
 
LVL 7

Author Comment

by:EYoung
Comment Utility
Raised points
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 7

Author Comment

by:EYoung
Comment Utility
The group policy part is greyed out and does not allow me to make any changes.  Currently the "enable trusted authentication..." section allows for Administrators but not any one else.  I guess I would want to add "Everyone" but no idea about how to do that.
0
 
LVL 7

Expert Comment

by:macentrap
Comment Utility
you have to be administrator to do this

http://technet.microsoft.com/en-us/library/cc738491(WS.10).aspx

the one which is mentioned by Sarika is done using
gpedit.msc


Screen-shot-2010-08-17-at-8.36.2.png
0
 
LVL 7

Author Comment

by:EYoung
Comment Utility
Yes, I am the administrator of the file server that hosts SQL Server and I did use gpedit.msc.  Also, that is the line that I clicked on.  It only has "administrators" to the right in the security setting.  When I click Properties, the Add or Edit button is greyed out.  I think I would need to add "everyone" or something like that.
0
 
LVL 7

Expert Comment

by:macentrap
Comment Utility
Can u check if  your account in DC

Active directory User and computer

ur Account / properties

what are the settings for

 trusted for delegation And
 account is sensitive and cannot be delegated


Active directory User and computer

same goes with computers
right click on computer name properties
 trust computer for delegation
0
 
LVL 7

Expert Comment

by:macentrap
Comment Utility
with gpedit.msc you are just looking at the local policy


this is greyout bcoz its superseded by  domain controller policy

gpmc.msc (if installed) or admin tools
domain controller policy

Screen-shot-2010-08-17-at-10.19..png
0
 
LVL 7

Author Comment

by:EYoung
Comment Utility
OK, I downloaded, installed, and ran gpmc.msc and was able to add 'Everyone' to the Enable computer... option. Those were good directions macentrap.  I especially appreciated your showing the image which made it easier to follow.

When I ran my VB 6 program, I still am getting the following error:
"[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

I guess I need help with "enable trusted authentication on my SQL Server"?

Thanks for sticking with this.  I really appreciate all the help.  Am bumping the points.
0
 
LVL 7

Expert Comment

by:macentrap
Comment Utility
you r welcomeEyoung!!
please, can you share you the connection string.
0
 
LVL 7

Author Comment

by:EYoung
Comment Utility
Here is the connectioin string I tried to use with the ODBC DSN (DSN-ANTS), but it does not allow me to start the program:
cnANTS.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=DSN-ANTS"

Here is the connection string that does work and allows me to connect but it fails when trying to read a large SQL table.  This connection string does not use the ODBC DSN:
cnANTS.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=ANTS;Data Source=AIMSERVER"

0
 
LVL 7

Expert Comment

by:macentrap
Comment Utility
try this one first!!

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Data Source=tcp:TcpIpAddress,port




0
 
LVL 7

Expert Comment

by:macentrap
Comment Utility
also try to use the fully qualified DN or ip address.... instead of computer name
0
 
LVL 4

Expert Comment

by:Sarika30
Comment Utility
Create a text document and name it as Sample.udl
Now open this and add your requirements in the Provider and Connection tab. Then test connection.
If this is success, right click on Sample.udl and open with notepad. Then use the connection string to connect to your server.
0
 
LVL 4

Expert Comment

by:Sarika30
Comment Utility
In your case In the Provider tab, use Microsoft OLE DB Provider for SQL Server.
In the Connection tab, Enter the server name. Then select the option "Use a specific user name and password". Enter the user name and password and then press Test Connection.
0
 
LVL 4

Expert Comment

by:Sarika30
Comment Utility
In your case In the Provider tab, use Microsoft OLE DB Provider for SQL Server.
In the Connection tab, Enter the server name. Then select the option "Use a specific user name and password". Enter the user name and password and then press Test Connection.
0
 
LVL 7

Assisted Solution

by:macentrap
macentrap earned 200 total points
Comment Utility
its more to do with Network-related or domain-related causes

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q328306
0
 
LVL 7

Author Closing Comment

by:EYoung
Comment Utility
Thank you both for the help.  It turns out to be more related to a network-domain issue that I was able to resolve with your help.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now