Re: Sql Server Login

From: Andrea Montanari (andrea.sqlDMO_at_virgilio.it)
Date: 01/04/05 

Date: Tue, 4 Jan 2005 17:47:14 +0100

hi,
"Hemang Shah" <v-hshah@microsoft.com> ha scritto nel messaggio
news:%23Qugnxg8EHA.2032@tk2msftngp13.phx.gbl
> Hello
>
> When i'm using Server Explorer, I have a small red x next to all the
> databases, and one of the server has an icon with a plug, Now I have
> "Use Windows NT security" selected. How can I use the login and
> password.
>
> I had installed MSDE before few months, I tried SA with blank
> password and that didn't work. Is there any installation log file ?
>
> What is the difference between these 2 security settings ?
>
> Which is better for creating the database on one computer and then
> deploying it at the customer site.
>
> Are there any documents which explain how to copy a database from
> development site to production site?
>
> Thanks

in order to use SQL Server authentication, and thus providing yourself the
login credential you need to, you have to set the SQL Server/MSDE instance
to allow that... by default MSDE installs on WinNT platform allowing only
WinNT (trusted) connection, and you have to "hack" the Windows registry to
modify it after instal..
please have a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;285097 for futher
info about modifying this behaviour..

SQL Server uses a so called "2 phase" authentication policy:
first an SQL Server Login or a Windows login must be created of granted
access to the SQL Server instance... at the server level a login can be made
member of none, 1 or all of the fixed server roles, which include "sysadmin"
role and so on...

the second authentication phase is at database level, where each login will
be granted database access mapping to a database user... here access
permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so on)
privileges at an object level (or column level for tables and views)..
the mapping is performed in the JOIN database..sysusers.sid =
master..syslogins.sid , so the only link is the provided Login's sid, it's
Security IDentification number
so, the second phase regards a database security implementation... in order
to access a specified database the simple login existance does not provide
database access, but a (database) user must be mapped to the corresponding
login.. and is about verifying that at each object level (including
database, tables, views, columns, procedures and so on) the Login/User
association is permitted access to... please go on reading at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0n77.asp
and following chapters..

but, back to the first phase, you can choose between 2 authentication modes:
WinNT (trusted) connections or SQL Server authenticated connections... the
latter always requires full user's credential such as "User
Id=sa;Password=pwd", the password can be NULL so it must not be specified,
but I strongly advise you always to ensure strong passwords are present....
WindowsNT authentication, on the contrary, does not requires user's
credential becouse it's directly provided by Windows via the logins'ID
(sid), which authenticate user's login at the windows login step... SQL
Server only needs to verify that the corresponding login and/or group is
granted to log on the instance...

Microsoft recommends to use the Windows NT (trusted) model as it grants more
and reliable security patterns
you can start reading about authentication modes at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_47u6.asp

other articles worth reading can be found at
http://www.sql-server-performance.com/vk_sql_security.asp
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec03.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec00.mspx 

-- 
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm        http://italy.mvps.org
DbaMgr2k ver 0.9.1  -  DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

Relevant Pages

  • Re: Install MSDE w/ MSDE Depl.Toolkit. What permissions when using Win Auth?
    ... I created a login using Enterprise Manager on SQL Server on my server. ... Database Access tab I ticked the tick box for the database that I ... Now when my user installed the MSDE database on his machine locally, ...
    (microsoft.public.sqlserver.msde)
  • Re: Directory Services, LDAP or similar
    ... In other projects, we managed the user authentication by creating tables that define all users and its allowed capacities, then the application queryies that data to verify if a user has access to some feature or not. ... The above ID and password are sent to the service at login time. ... They are using Novell eDirectory at the enterprise level; yes it's LDAP. ... We already do that for three different DB servers; ...
    (borland.public.delphi.non-technical)
  • Re: Is there malware on my Server?
    ... be exposing to the internet some means of authenticating to the server. ... Anonymous Access is checked and the login uses the ... Integrated Windows Authentication is checked. ... Administrador indicates hack attempts to log on with the Administrator ...
    (microsoft.public.windows.server.security)
  • Re: SQL Server 2005 Express Remote connection
    ... I have also deleted the login for Fred from the list of users for my ... So Fred no longer exists as a login or as a user in the SQL Server setup. ... the database? ...
    (microsoft.public.sqlserver.server)
  • Re: Directory Services, LDAP or similar
    ... we managed the user authentication by creating tables ... The above ID and password are sent to the service at login ... Novell eDirectory at the enterprise level; yes it's LDAP. ... servers; ...
    (borland.public.delphi.non-technical)