asked on

MS SQL Server 2000 Login - Connection failed

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

macentrap

have you added the NT user into security Login through enterprise Manager and given database access

macentrap

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.

Sarika30

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.

Guy Hengel [angelIII / a3]

please read this article to understand and troubleshoot that kind of problems:
https://www.experts-exchange.com/A_1881-Connect-to-your-SQL-Server.html
https://www.experts-exchange.com/A_1881.html

EYoung

ASKER

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.

Sarika30

Are all your machines in different domain or same domain.

Sarika30

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.

EYoung

ASKER

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.

macentrap

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

ASKER CERTIFIED SOLUTION

Sarika30

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EYoung

ASKER

Sarika30: Where in the server's Active Directory tool do I make those changes? Thanks

EYoung

ASKER

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

EYoung

ASKER

Raised points

EYoung

ASKER

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.

macentrap

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

EYoung

ASKER

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.

macentrap

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

macentrap

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

EYoung

ASKER

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.

macentrap

you r welcomeEyoung!!
please, can you share you the connection string.

EYoung

ASKER

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"

macentrap

try this one first!!

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

macentrap

also try to use the fully qualified DN or ip address.... instead of computer name

Sarika30

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.

Sarika30

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.

Sarika30

SOLUTION

macentrap

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EYoung

ASKER

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.