SQL 2000 setup 1

[PhatH]

Hello all,

My ASP pages was working ok with sql 2000 database server until we replaced the server w/ a new one and re-download SQL 2000 to the machine.

New database and tables can be easily created by using WINDOW AUTHENTICATION, but NO userID and password are required to view tables, which creates a conflict with a database connection file:
<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" &_
"Server=*******;" &_
"Database=********;" &_
"Network=DBMSSOCN;" &_
"Uid=**;" &_
"Pwd=*****;"
%>
hence, I got a ERROR:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '**'.
/includes/dbconn.asp, line 3

I tried to create another database by using SQL SERVER AUTHENTICATION; however, "log in failed for user '**'" and it keeps failing with any other choice I have.

Could someone tell me where have I gone wrong??? Thanks!!!

 

[MDXer]

what authentication mode is the server configured to use? Mixed or NT Auth?

&quot;Shoot Me! Shoot Me NOW!!!&quot;
- Daffy Duck

 

[PhatH]

NT Auth

 

[SQLBill]

You created a new database and tables, but did you create LOGINs (users) for the database? And did you make sure they are set for Windows Authentication?

-SQLBill

 

[PhatH]

Our network guy just download SQL2000 fresh to the server.

All I did was just download SQL2000 Client to my computer and GOTO NEW SERVER REGISTRATION... from SQL SERVER GROUP to link to our network server where SQL2000 Server located.

After I selected the AVAILABLE SERVER given, added to ADDED SERVER. And at the CONNECT USING, you now can look back into my first note to understand the rest...

 

[SQLBill]

There aren't any Windows Authentication users on your databases, so you can't connect that way.

Change the authentication to MIXED (both SQL Server and Windows). Then register the server using login SA and a blank password (that's the default, your installer MAY have given SA a good password - ask that person). Once you register the server and have access via SA account, add additional users which will have Windows Authentication. Then switch the database security to Windows Authentication Only.

-SQLBill

 

[PhatH]

Good morning...

I right-clicked, PROPERTY, the setting was at BOTH SQL Server and Windows. On the bottom Start Service Account, I selected This Account, entered SA and cleared Password area blank, then hit the OK button. ERROR: Cannot use the reserved user or role name 'SA'.

I'm not a SQL guy, so please be a little more in detail so I can follow... Thanks for all of your help!!!

 

[PhatH]

Update!!!

After adding a new database, I noticed there is an USER(dbo) with an 'sa' login name.

I hope this will help you understand better.

 

[SQLBill]

To use the SA account, leave the System Account (SA) checked. That's how you set the account that starts the 'services' (right click on MyComputer, select Manage, drill down to Services - SQL Server uses two services to run MSSQLServer service and SQLServerAgent service. If you have multiple instances, there will be one of each of the services for every instance.

The default database owner (dbo) is always SA until you change it to a different user.

Are you on the same network as the SQL Server machine? If not, then you can't use Windows Authentication. You must have a trusted network to Win Auth. If so...

Let's try this...using Enterprise Manager, expand everything until you see Security. Expand that. Right click on Login. Select New Login. Create a new login and make sure you set it for SQL Server Authentication. Then on the database tab, select the database(s) that this login will have access to (check the box to the left of the database). Then go down to the bottom window and give the login access (for now make it db_datareader).

Now try to access the database using that login and password.

-SQLBill

 

[PhatH]

Perfect...

Thank SQLBill very much... and anyone else as well!!!

 

[SQLBill]

Did that work then?

-SQLBill

 

[PhatH]

the last instruction going to SECURITY to create a new user working great.

Thank SQLBill !!!!

 

[SQLBill]

You're welcome, thanks for the star.

-SQLBill