sborchers
asked on
Windows 2000 Login for SQL Server 2000
I have an application that was developed using VB 6 and uses SQL Server 2000 as the database. I am using SQL Server authentication in my code to access the data and the SQL Server user is setup and has the correct rights to the tables and stored procedures. The Windows 2000 Server running SQL Server is part of a workgroup, not a domain. I am running the application from computers on a seperate domain. I can connect to the SQL Server fine if the login on the computer running the application is the same login name and password, as on the Windows 2000 Server. If the user's login doesn't exist or the password is different then I cannot connect to the SQL server in my application. Is there a way to work around this security issue. I'm confused because I assumed that if the SQL Server authentication user is setup on the SQL Server I should be able to get to the data reguardless of who the Windows login is.
Thanks,
Scott
Thanks,
Scott
It sounds like the application itself is sending the logged in credentials. If that is the case you will need to code it to pass credentials the user supplies, not the credentials of the logged on user.
Are you sure that your SQL server is not in mixed authentication mode?
ASKER
msice,
Yes my server is in mixed authentication mode. I am passing the SQL Server user name and password to access the database. If the a user tries to access the data with a username and password that are the same as a username and passoword setup on the Windows Server running the SQL Server then I can connect to the SQL Server. If the username and password do not exist on the Windows Server running the SQL Server then I can't connect to the SQL Server.
Yes my server is in mixed authentication mode. I am passing the SQL Server user name and password to access the database. If the a user tries to access the data with a username and password that are the same as a username and passoword setup on the Windows Server running the SQL Server then I can connect to the SQL Server. If the username and password do not exist on the Windows Server running the SQL Server then I can't connect to the SQL Server.
ASKER
marc nivens,
I am passing the correct credentials to access SQL Server using SQL Server authentication. The only way I know how to pass the correct credentials to the Windows server is by mapping a drive in code, but I'm not sure I want to do that becuase I have over 50 pc's with different mapped drives.
I am passing the correct credentials to access SQL Server using SQL Server authentication. The only way I know how to pass the correct credentials to the Windows server is by mapping a drive in code, but I'm not sure I want to do that becuase I have over 50 pc's with different mapped drives.
Have you thought about using SQL Server only authentication.
ASKER
The only two authentication modes are SQL Server and Windows or Windows only.
Are you using an ODBC connection?
ASKER
Yes, I am using an ODBC connection.
If you connect through ODBC, in the ODBC Administrator, choose SQL Server authentication when you configure the data source.
"I'm confused because I assumed that if the SQL Server authentication user is setup on the SQL Server I should be able to get to the data reguardless of who the Windows login is." - Yes you should. In the ADO connection string, where you include the parameters "UID" and "PWD" when you use ODBC, and "User ID" and "Password" when you use the SQLOLEDB provider set it to the sa user and pass for all and work backwards from there.
ASKER
I am doing that currently. I am using SQL Server Authentication in my ODBC, but if I don't have the username and password setup on the Windows 2000 Server for the user of the computer that is trying to run the application then I receive an error. I'm not sure if the problem is because I'm trying to run the application in a domain and the WIndows/SQL Server is in a seperate workgroup, but it is a problem.
ASKER
Good thought, I'll try that. And yes I thought I should be able to connect regardless of the windows user.
Thanks
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Client OS we are working with currently is Windows 200 Pro, but when live we will be using Win95,98 and 200Pro. The sa user still did not work. I think I'm going to chase the named pipes IPC issue.
ASKER
The Guest account wan't enabled. I enabled it and the connection worked. Exactly what I was looking for. I new there had to be something with the NT security.
Thanks,
Scott
Thanks,
Scott
Yes and take a look at the 98/95 issues as well they will require attention.
Cool glad to help and that you got it working.
Thank You for the points and the grade.
Thank You for the points and the grade.