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
LVL 7
EYoungAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

macentrapCommented:
have you added the NT user into security Login through enterprise Manager and given database access
macentrapCommented:
login information is stored in master database
macentrapCommented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Sarika30Commented:
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]Billing EngineerCommented:
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
EYoungAuthor Commented:
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.
Sarika30Commented:
Are all your  machines in different domain or same domain.
Sarika30Commented:
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.
EYoungAuthor Commented:
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.
macentrapCommented:
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 
Sarika30Commented:
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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EYoungAuthor Commented:
Sarika30:  Where in the server's Active Directory tool do I make those changes?  Thanks
EYoungAuthor Commented:
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
EYoungAuthor Commented:
Raised points
EYoungAuthor Commented:
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.
macentrapCommented:
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
EYoungAuthor Commented:
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.
macentrapCommented:
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
macentrapCommented:
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
EYoungAuthor Commented:
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.
macentrapCommented:
you r welcomeEyoung!!
please, can you share you the connection string.
EYoungAuthor Commented:
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"

macentrapCommented:
try this one first!!

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




macentrapCommented:
also try to use the fully qualified DN or ip address.... instead of computer name
Sarika30Commented:
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.
Sarika30Commented:
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.
Sarika30Commented:
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.
macentrapCommented:
its more to do with Network-related or domain-related causes

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q328306
EYoungAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.