Can I avoid entering a SQL Server password for MS Query

[niallo32]

I have an Excel 2000 query through MS Query which connects to SQL Server 2000.

when I run the query, it throws up the error below and but and asks for a username & password. When I select 'trusted connection' or enter in their ERP login, it returns the following error:

Connection failed: SQLState: '28000'. SQL Server error : 18456 - login failed for user Domainname\username

When I go to SQL Enterprise Mgr/Properties/Security/Authentication = SQL Server & Windows

Is there a way that I can enter in the SQL Server authentication details once and 'hardcode' them as such, so that they are cached and the user doesnt have to reenter them each time they run the query?

Clients connecting are on a Windows 2000 Terminal Server SP4

Thanks

 

[niallo32]

Can anyone help????

 

[SQLBill]

Is this for clients on the same domain? If so, consider making their SQL Server accounts - Windows Authentication. Then they shouldn't need to enter a password.

-SQLBill

Posting advice: FAQ481-4875

 

[niallo32]

The Windows authentication field is greyed out here and I dont appear to be able to change it - do you know how I can 'ungrey' this?

Thanks

 

[SQLBill]

You would have to drop their current (SQL Server Authentication login) and recreate it as a Windows Authentication.

BUT for Trusted Accounts to work, they must authenticate with the same domain controller(s) as SQL Server uses.

-SQLBill

Posting advice: FAQ481-4875

 

[niallo32]

I assume you mean delete when you say drop?

If so, can I just leave the existing SQL account in place and create a new trusted account ?

Thanks

 

[SQLBill]

Only if you give the user a new login name.

-SQLBill

Posting advice: FAQ481-4875