SQL Server 2000 ODBC related issues...

[rwyarger@hotmail.com]

I have a customer who has a SQL Server 2000 DB and we are needing to
get to this DB through ODBC. I had setup a DSN last week that
connected fine to this DB, and was preparing to build a script to
manage transfers of data to and from one table to another.

The then began to have troubles with their DB Backup (this is not at
all related to the issue at hand). The problem they claim was that the
"sa" was needing to have certain roles applied to it that were not part
of it's default properties. My understanding is that the "sa" is the
King of the Kingdom sotospeak, and has all rights, permissions, role
capabilities, etc...

To make a long story short she tried to apply the write and read roles
to the "sa" and it would not allow for this to be applied. She then
applied a password to the "sa" account (previous it did not have one
and was set to blank) and upon doing this she then removed the password
and set it back to blank.

Since she made these changes we have not been able to connect to her DB
via our DSN. When we get to the point of telling the DSN to use SQL
Authentication, and apply the sa account for use with a blank password
- it comes back with an error similar to "database is not available or
password is incorrect".

Has anyone had this problem before, and if so is there any simple
resolve? Something other than the customer having to restore a previous
backup of the DB?


Much thanks in advance...

[Simon Hayes]

Re: SQL Server 2000 ODBC related issues...

It sounds to me as if the sa password is not blank - have you tried
connecting from osql.exe or Query Analyzer to confirm this? If you also
get a login error in those tools using a blank password, then it's
almost certain that the account does have a password set.

But in any case, the sa account should always have a password set, and
it should not be used for applications - see "System Administrator (sa)
Login" in Books Online, which says that sa is for backwards
compatibility only.

You're correct that there is no permissions checking for sa, as it has
full permissions already, and this is why it's too dangerous to use it
for applications. Even DBAs don't normally need to use it - they can
use another login which has been added to the sysadmin role.

Probably the best solution is to create a new login for your
application, and give it only the permissions it needs - see "Managing
Security", sp_addlogin, sp_grantdbacces s, GRANT and "Using Ownership
Chains" in Books Online to get an idea of how permissions work.

Simon

[rwyarger@hotmail.com]

Re: SQL Server 2000 ODBC related issues...

Simon thank you for the quick response. Unfortunately the application
in question has to use "sa" (there is no way around this). I will have
her run osql.exe and Query Analyser to see if she gets the same
response. Let me ask you this though - if she is able to get into
either or both of those methods with the "sa" having a blank password -
what then would be the reason why a User or System DSN would be still
having trouble?

Any thoughts on that would be great, but I will check for now with
these other 2 apps.

[Erland Sommarskog]

Re: SQL Server 2000 ODBC related issues...

(rwyarger@hotma il.com) writes:[color=blue]
> Simon thank you for the quick response. Unfortunately the application
> in question has to use "sa" (there is no way around this).[/color]

It sound to me that you had the connection details in a DSN? In such,
isn't it as easy as changing the DSN?
[color=blue]
> I will have her run osql.exe and Query Analyser to see if she gets the
> same response. Let me ask you this though - if she is able to get into
> either or both of those methods with the "sa" having a blank password -
> what then would be the reason why a User or System DSN would be still
> having trouble?[/color]

I will have to admit that I never fully understood DSNs, and they were
always a hassle. Most applications today do not use DSNs.

One thing to try is a password of one single space.

In any case, I would strongly recommend that you do not have a blank
password for sa.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp