SBS 2K3 Premium - Setup a DB on SQL 2000 & test it

We received a request to setup a database on the default SQL 2000 instance and verify connectivity.

We went through the following steps (change italics to your own names):

1. Server Management Console
2. Advanced Management
3. Computer Management
4. Services and Applications
5. Microsoft SQL Servers (local) (Windows NT)
6. Databases
7. Right click: New Database
8. Name: mynewdb
• Leave the defaults
9. Down to: Security
10. Logins
11. Right click: New Login
12. Name: newdbuser
13. SQL Server Authentication with password: dbpassword01
14. Database: mynewdb
15. No Server Roles
16. Database Access Tab: check mynewdb
17. Check: db_owner
18. OK

Your database should now be setup and ready to role. Or at least it should.

Next step, at the SBS server from the command line we test connectivity to the database:

osql -U newdbuser -P dbpassword01 -d mynewdb

Here is the result of that osql command:

Login failed for user 'newdbuser'. Reason: Not associated with a trusted SQL Server connection.

A search of the Microsoft KB turned up the following articles:

Microsoft KB 889615: You may receive a "Not associated with a trusted SQL Server connection" error message when you try to connect to SQL Server 2000 or SQL Server 2005.

and

Microsoft KB 555332: Login failed for user 'username'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452).

The second article contains the solution:

Switch the authentication mode to SQL Server and Windows:


Note that the change requires a restart of the SQL Server services. If there are any client databases online at the time, either everyone needs to shutdown their access, or we need to wait until later on when no connections to any databases on the server are happening.

Also note that we did not receive any indication as to why the database was not accepting connections via the SQL Enterprise Manager on another workstation. It just refused to connect which is why we went to the osql commands directly on the server.

Some links:

When looking for the commands we needed on the command line, it was a bit of a trial at first. We finally came up with: MSDN Forums - Command Line connect to SQL Server Database.

That in turn led us to:

The osql Utility and its commands on MSDN.

Lots of database fun on this one! :D

Philip Elder
MPECS Inc.
Microsoft Small Business Specialists