Authentication Error using SA account

Authentication Error using SA account

Post by Ange » Sun, 11 Mar 2001 04:13:40



Hi! I've looked everywhere to find some sort of explanation for this
problem, but I was unsuccessful!  In short: I've create a database on local
MS SQL 7 for development. Once finished I've created a database on
remote/live server, used the export function of Enterprise Manager to
transfer data/tables/sp and so on.  I also created a new user and gave him
dbo rights to the new database, as well as I went to the table and sp's
properties and gave that user create/execute/alter permissions. When trying
to connect to live website that access the newly created db I get this
error:
 Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'test'.

So I figured there's some problem with the new user I created, I've tried
connecting with valid SA account, got same error.  I've tried connecting
through query analyzer with sa, as well as the user test accounts and had no
problems.  But no matter what I did when trying to connect through asp using
ADO it would continue to fail.  The weird part was that I had other db's on
the same sql server and previous site that I created on the same Web server
I was trying to make the connection from still connect fine to those
databases. But any new db's that I created I was unable to access with SA or
any other account. Taking the test further I've tried putting up another
server, this time MS SQL 2000 on the same network as web server, and running
a simple test.  I've wrote an asp page that connected to that 2000 sql
server to db pubs using SA account, and it still failed with the above
error.  Being desperate, I've put up another server on totally remote
location, no residing on this network, installing MS SQL 2000, but when
creating sa user, I left the pw blank.  To my surprise I was able to connect
to that db from those websites I've put up earlier using the SA account.  At
this point I can't really say whether it's a SQL server issue, IIS issue, or
Windows Permissioning issue. I've checked windows permissions and on all
occasions it said full control to Everyone.  Here's the connect string I
used in ASP which fails on all new efforts I attempt to first 2 sql servers,
but yet works on older sites I've designed that connect to those same sql
servers.  Some one PLEASE HELP! This been going on for 2 weeks and I'm
loosing hair over it now!
(note I've put a fake ip here, but in the code it's a valid ip)
Conn="Server=127.0.0.1;Driver={SQL Server};Database=test;UID=sa;PWS=sapw;"

Thanks in advance!

 
 
 

1. SA account and SA role

I've been a Sybase DBA for a while, but just got started in MS SQL.

In Sybase, we have the concept of roles, like the sa_role, sso_role, and the
oper_role.

If I assign the sa_role to a login, then that login can do anything that the
sa account can, enabling me to lock the sa account for security reasons.

We just acquired a small software company that has been using MS SQL 6.5,
but they use the sa account for EVERYTHING.  Since I inherited the DBA job
for this MS SQL Server, I wanted to assign myself sa_role and disable the sa
account, but I cannot find any information on how to do that in SQL 6.5.

Help.

They need to use integrated security, which I believe maps anyone in the NT
administrators group to automatically login as sa.  How can I go around
that?  I don't want anyone to login as sa.  Never!

Thanks in advance.

-criatura


2. SQL loss of data when restarting SQL service?

3. Help Login fail using sa account

4. Multi-user and TrueGrid

5. SQL Server "sa" account errors

6. SQL View Question

7. sa account error

8. order by

9. Accessing SQL 7.0 through ASP using SQL authentication rather than NT authentication

10. attempts to logon using SQL Server authentication treated as Windows authentication

11. MS Great Plains - sa and Windows Authentication

12. SQL 2K NT Authentication mode and sa login

13. sa authentication