LRE: [dba-SQLServer] Problems registering database

John W. Colby jwcolby at colbyconsulting.com
Mon Oct 11 21:00:20 CDT 2004


Andrew,

>its that you don't have permissions as its assuming you're using the 
>Guest account when using Windows (Integrated) Authentication.

And why is it assuming that?  Is there any way to specify an account?

>Set it up to use SQL Authentication - specify the same password for the 
>sa account so that you only have to remember one, and then use these 
>credentials when registering servers from the other machines.

I am told that SQL Authentication is a security risk for reasons unknown (to
me).  Besides which, 

>Set it up to use SQL Authentication - specify the same password for the 
>sa account so that you only have to remember one, and then use these 
>credentials when registering servers from the other machines.

Is just vague enough that I haven't been able to figure this out.  When I
installed SQL Server it asked me what I wanted.  I specified Windows login
authentication.  I have NO CLUE how to "set it up to" do anything.  I can
see where I "set up a new user" but that sure doesn't appear to be what I am
after.

I have searched BOL and found all kinds of stuff, but not how to go in and
tell SQL Server to switch from using Windows Authentication to SQL Server
Login (or both).

***********
SQL Server Authentication Modes
When SQL Server 2000 is running on Windows NT or Windows 2000, members of
the sysadmin fixed server role can specify one of two authentication modes: 

Windows Authentication Mode 
Only Windows Authentication is allowed. Users cannot specify a SQL Server
2000 login ID. This is the default authentication mode for SQL Server 2000.
You cannot specify Windows Authentication Mode for an instance of SQL Server
running on Windows 98, because the operating system does not support Windows
Authentication.

Mixed Mode 
If users supply a SQL Server 2000 login ID when they log on, they are
authenticated using SQL Server Authentication. If they do not supply a SQL
Server 2000 login ID, or request Windows Authentication, they are
authenticated using Windows Authentication.

These modes are specified during setup or with SQL Server Enterprise
Manager.
************

Well isn't THAT just wonderful?  I now know that it can be done with EM.

I have gone in to EM, to the properties window, security tab.  I tell it SQL
Server and Windows.  I tell it to start and run SQL Server using
Administrator and the password.  I close and restart (and even reboot).  I
switch to another computer.  I try to attach a server.  I give it the
Administrator user name and the password (SQL Server login using information
provided by the system administrator) and it fails with "Login failed for
user Administrator".

I don't know what else to do here.  I am NOT a Database Administrator, I am
a poor shmuck trying to get my systems to play.  Suggestions like "set it up
to use..." just don't help me.  Things like 

"Open EM"
"Click on..."
"click on..."
"enter..."

THAT would help me.

Thanks,

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/





More information about the dba-SQLServer mailing list