SQL Server Security
We required a mapping Login to connect to the server either from Person / Application .
In SQL Server Logins can be of 2 types .
* Windows Authentication Logins
* SQL Server Authentication Logins
Windows Authentication Logins
* These are created for the active directory users .
* Using a single sign on we can connect to Windows and SQL Server .
* Windows Password policies are applicable .
* Provides more security.
* The windows authentication logins information will not be maintained by SQL Server .
How Windows Authentication Works
* When a user is connected with an active directory user to the mechine then windows will verify the user name and password and allocated an Unique ID called TGT (Ticket Granting Ticket ) .
* When the same user connected to SQL Server with the TGT he will be allowed to connect SQL Server.
How to create user in Active Directory
Step-1 : Create an Active Directory User
Start -->Control Pannel --> Administrative Tools --> Active Directory Users & Computers
(or)
Start --> Run --> dsa.msc
Select the user folder right click and click on New --> User
Provide the first name , initial ,last name and user logon name and click on next
Provide the password confirm the password and click on next and click on finish
Step-2 : Creating windows login in SQL Server
In the object explorer under the server expand security folder
Select the login folder right click and click on new login
Click on search click on advanced and click on find now
select the required active directory user and click on ok and click on ok
we can also create window login with T-SQL command
User Master
go
Create Login <Login Name> from windows
Orphan Logins
When an active directory user is deleted then the mapping login in SQL Server is called as Orphan Login .
To see the list of Orphan Logins
sp_validatelogins
Note: To resolve the orphan logins create the active directory user
SQL Server Authentication Logins
* These logins are created for the non-active directory users.
* The users requires a separate login and password to connect with SQL Server.
* By default every instance contains a default SQL Login 'sa'
* The information about the SQL Logins and passwords will be maintained in SQL Server.
How SQL Server Authentication Works
* The user will connect to the local machine by using the local credentials .
* By using an SQL Server login and password the user connects to SQL Server.
* SQL Server verifies the validity of Login and Password.
Creating of SQL Server Logins
* In the object explorer under the server expand the security select the login folder right click and click on new login.
* Provide a login name select SQL Server authentication .
* Provide password and confirm password.
* Select the options to enforce the password policy.
* Click on OK
Note:
* By default every login can access master , msdb & tempdb databases .
* But user and model database are not accessible .
We required a mapping Login to connect to the server either from Person / Application .
In SQL Server Logins can be of 2 types .
* Windows Authentication Logins
* SQL Server Authentication Logins
Windows Authentication Logins
* These are created for the active directory users .
* Using a single sign on we can connect to Windows and SQL Server .
* Windows Password policies are applicable .
* Provides more security.
* The windows authentication logins information will not be maintained by SQL Server .
How Windows Authentication Works
* When a user is connected with an active directory user to the mechine then windows will verify the user name and password and allocated an Unique ID called TGT (Ticket Granting Ticket ) .
* When the same user connected to SQL Server with the TGT he will be allowed to connect SQL Server.
How to create user in Active Directory
Step-1 : Create an Active Directory User
Start -->Control Pannel --> Administrative Tools --> Active Directory Users & Computers
(or)
Start --> Run --> dsa.msc
Select the user folder right click and click on New --> User
Provide the first name , initial ,last name and user logon name and click on next
Provide the password confirm the password and click on next and click on finish
Step-2 : Creating windows login in SQL Server
In the object explorer under the server expand security folder
Select the login folder right click and click on new login
Click on search click on advanced and click on find now
select the required active directory user and click on ok and click on ok
we can also create window login with T-SQL command
User Master
go
Create Login <Login Name> from windows
Orphan Logins
When an active directory user is deleted then the mapping login in SQL Server is called as Orphan Login .
To see the list of Orphan Logins
sp_validatelogins
Note: To resolve the orphan logins create the active directory user
SQL Server Authentication Logins
* These logins are created for the non-active directory users.
* The users requires a separate login and password to connect with SQL Server.
* By default every instance contains a default SQL Login 'sa'
* The information about the SQL Logins and passwords will be maintained in SQL Server.
How SQL Server Authentication Works
* The user will connect to the local machine by using the local credentials .
* By using an SQL Server login and password the user connects to SQL Server.
* SQL Server verifies the validity of Login and Password.
Creating of SQL Server Logins
* In the object explorer under the server expand the security select the login folder right click and click on new login.
* Provide a login name select SQL Server authentication .
* Provide password and confirm password.
* Select the options to enforce the password policy.
* Click on OK
Note:
* By default every login can access master , msdb & tempdb databases .
* But user and model database are not accessible .
No comments:
Post a Comment