SQL Server 2000 Oddness

I recently updated SQL Server 2000 to SP4 for an application and was reworking the OBDC driver to use an IP rather than DNS name to hit the server.

Only issue is now when I test the connection it fails for every user except if I log in as my domain account and set the driver then. But if I log in then as anyone they fail authentication.

I have the following checked in OBDC:
With Windows NT Authentication using the network ID
Connect to SQL Server to obtain default settings for the additional configuration options

If I choose the second option to manually use a password no credentials I use work.

The SQL Server Authentication is set to SQl Server and Windows
Nathaniel_ScrivNETAsked:
Who is Participating?
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.

arnoldCommented:
usually, a system DSN uses the SQL login unless you configured access to the database based on windows accounts.
Check the security sections on the database server to see whether you have domain users as able to login and have the same rights as a DBO to the database in question.

Nathaniel_ScrivNETAuthor Commented:
ahhh. Is there any way to see this password? If not, how do I reset it and what username would I use?
sammySeltzerCommented:
When you open dsn, what username do you see?

Also, you cannot see the password. You can, however reset it.

So,on the SSMS sql server explorer window, expand Security, expand logins and you should see all the users.

If a doman account, it usually begins with DomainName\Username.

Click on that and you see a bunch of dots, .......

replace those with whatever password you wish to reset the existing one with.

Of course map it to your db
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Nathaniel_ScrivNETAuthor Commented:
When I open system DSN, the two options I explained before are checked. The username in the field is always the id of the current user with no password. It's the same on machines I haven't touched yet that still work, so it's apparently just not showing the current login.

I went to where you said in security and it indeed shows all users are governed by SQL Server Auth.

I think I have to reset the DQL password and reconfigure all the obdc settings. How does one reset this master password?
arnoldCommented:
It depends on your setup.

Within enterprise manager, you can go to security\logins to see what accounts there are sa, domain users, etc.
You would then look at the security within the database\users and see which username/s has DBO role.
The sql login is usually part of the application that is used.
I.e. the application relies on the system DSN for the connection but provides the SQL login credentials that are obtained via a configuration file (encrypted connection string) to access.

When setting the DSN, choose the sql login option and that should do it. You can provide the sql login username/password if you want to test the connectivity.

Many things depend on which sql user's password you are planning on changing? SA, or a specific user.  Caution must be taken when changing SA password in the event some application is using the sa account which would mean you would have to update that application.

It is often best practice to use a specific account for a specific purpose versus using the SA account.
Nathaniel_ScrivNETAuthor Commented:
The users that have the sysadmin role are builtin/administrators, sa, and hisdba

That explains why I can use my domain account to connect but no others. I think I have to assume that the SA is being used in the obdc connection at this point. As the prior support for this client left no documentation on what it is I think this is the path I have to take.

It is the only role that has accounts in it.
arnoldCommented:
Do you have a system where this application works? Check the ODBC configuration there.
Alternatively/Additionally you can use the sql profiler when that session is active to see what username is being used for the login. I.e. you will see events generated by transactions from that system and you can see whether it is using an Sql user and which it is.
There is a way to map windows users to a specific sql user.
 
Checking the users listing within the database, would also tell you whether sa is the only account with DBO role or whether it is using hisdba.

The DSN does not retain the login credentials. Once configured the application using the dsn must porvide the username/passowrd or refer to Integrated security (which means it will pass the windows login credentials)
check the properties of the hisdba to see what mappings it has i.e. does it have a database of which it is listed as DBO?
Nathaniel_ScrivNETAuthor Commented:
I was thinking about and it just created another account that has the same rights as sa. The ODBC connection now works for all the db users.
Nathaniel_ScrivNETAuthor Commented:
@arnold

how do i use the sql profiler?
Nathaniel_ScrivNETAuthor Commented:
Ok the profiler shows the DB user that is logged in, but it does not seem to show the user account used to make the initial connection through odbc. Neat tool. I think the way this is set up is the individual dbusers have certain access once logg3ed in, but the ODBC credentials need to be there to get in at all.
arnoldCommented:
You open it, you then create a new trace and connect to the database using windows or sql login. Once there unless you want to use the data to optimize your server you can click on and you should see a window where the "Trace Start" and if your database is active you should start seeing lines being added.
There you will see columns for applicationname, ntusername, loginname.
Since NTuserName can be maped to a login name, if ntusername is present, you have a windows login. a loginname is an sql login.
Depending on what the application is, using another user with DBO rights is enough versus setting up another user that has sa (sysadmin rights) poses a risk that once that information is exposed, that sql user has full access to the database server i.e. create/delete databases,tables.
You should explorer pairing down the rights on this account to the point where it can provide the information without posing a risk.

You could create a new user, and on your own system try and see what rights beyond DBO it needs to function and then make similar changes to the account you referenced.

http://msdn.microsoft.com/en-us/library/ff650699.aspx
http://msdn.microsoft.com/en-us/library/ms181091.aspx
Nathaniel_ScrivNETAuthor Commented:
Good advice. I took the new user out of the sysadmin role and made sure it has db_owner and public access. I was able to make and delete entries so sysadmin role is probably a risk. The hisdba account actually had no mappings for access.
Nathaniel_ScrivNETAuthor Commented:
I noticed the other users only have public access by themselves. Does the new account in OBDC that has the DBO access grant that access through inheritance?
arnoldCommented:
Not sure what you mean.
an account configured with login rights into the sql server and mapping for dbo to a specific database provides access and ability to access/update data in a database.
The users using an application which references the DSN has to have a configuration setting where the username/password for the sql login is stored.  The information could be stored in an encrypted form. i.e. the application has a settings/option where you can configure the database connection.  Once the information is submitted the application encrypts the data and stores it on a disk (local or central share). The application can decrypt the data. Another option the application stores the complete connection string.
i.e. encrypt(DSN=mydatabase,User=sqllogin,PWD=password)
The output can be passed directly to the sql server which can understand the content.

Example http://chiragrdarji.wordpress.com/2008/08/11/how-to-encrypt-connection-string-in-webconfig/

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
Nathaniel_ScrivNETAuthor Commented:
The account used for OBDC has public and DBO access. The other user accounts have only public.

Once the user brings up the application they have to use their own individual account to log in. The ODBC connection seems to be the bridge between the login and the user. The users themselves do not have initial  DB access rights.
arnoldCommented:
There are two sets of logins.
One deals with the application gaining access to the data on the database this is the SQL login.

Within the application you can have an authentication requirement such that you can control which user can use the application and what actions they can perform within the application.i.e. within the database for the application you have a user, employee table which is the source of the username/password the user sees in order to use the application.

I.e. this is equivalent to a person walking into a building is first granted access into the building (SQL login) in order for the user to gain further access to offices, etc. the user must poses their own key (username/password prompt in the application). Within the application you can control what a user can do and what he can not do i.e. you have a table that enumerates what options/capabilities each user has.  This is the restrictive configuration i.e. the SQL login used to establish a connection to the database has rights to perform any and all tasks sent its way.  The application the user is using is the one that limits what the user can do.

hopefully, I made it clear versus making a circular argument.

two step process.
launch application which uses its configuration to establish a connection to the database server and database.
Step two, the user has the application up and running where the user must provide a username/password to gain access. This username and password is stored within the database on the server.


Nathaniel_ScrivNETAuthor Commented:
We're pretty much saying the same thing. You sir, are a gentleman and a scholar. Thank you!
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.