SQL Server Security Authentication Concept

 

Authentication:

• Authentication is one of the security concept in SQL DBA..
• Validating credentials(user name and password) on server called Authentication.For any user who are connecting to server will have login name and password.

        We have 2 types of logins:

1. Window login
2.  SQL Server login

1.Window login

• Domain user,Database Administrators,other admins,developers will have windows login for connect to server.
• These user use same account for connecting to windows and SQL Server.
• Windows login store in windows component is called active directory.
• this login provides high security.

2.SQL Server login:

• For the users who are connecting externally from frontend we are providing SQL login.
• They first connect to windows and then connect to SQL Server.These logins stores syslogins table of Master database.
• We need to provide security to SQL Server.We have to validate both windows and SQL logins.This process of checking login name and password is Authentication.

There are 2 types of Authentication in SQL Server.

1.Windows mode authentication

2.Mixed Mode:

1.Windows mode authentication:

• It allows only window login users in this mode.
• It enforces only windows user to connect and access the databases.
• When we use highly restricted database we go with windows login.
• We don't provide access to external users if the data is highly restricted.
• This mode provides best security.
• It validates logins in Active directory.

2.Mixed Mode:

• This mode allows both windows and SQL logins.
• When ever any login tries to establish connection first it validate user name and password in active directory.
• If login exists,establishes connection other wise validation goes to syslogins of master database and verifies there.
• If login exists it will establish a connection.
•     When ever any request comes first we need to verify the login active directory.If it is not exist we need to create (or) ask Active directory admins to create command for windows.

     CREATE LOGIN login_name { WITH <option_list1> | FROM <sources> }

Authentication at database level:

• Login Account is a key to Main door means to connect to server.It's a server level access.
• We require another account called user account to access the databases inside the server.This is a database level access.
• We need to grant access for these user account on each database separately.

User Mapping:

• Every  login account will be mapped with the user Account.
• Every login will have a security id(sid),its a unique security number inside SQL Server to avoid duplication of logins using the sid of login account.
• We will map to sid of user account.
• Now it establishes relationship between login account and user account,this is user mapping.
• One login account will mapped to one user account only We call as one to one mapping.
• Once the mapping is created between login and user account,SQL Server allows to connect to server and then to access the databases inside the SQL Server.

Creating Windows login:

           create login login name from windows

Creating SQL Server login:

1. For creating any login,It requires 4 properties
2. Login name
3. Password
4. Default database
5. Default language(optional)

Using T-SQL:

        sp_addlogin 'loginname' 'password' 'default database'

         ex: sp_addlogin 'sql2012admin' 'ravi@123' 'master'

Using GUI:

 go to security folder under management studio-->Go to login-->Right click on login-->Select New login.