SQL Server – Server Security – Part 1

Hello Folks,

You might have seen mine one blog-post, which deals with the Windows Security. Hence, this topic doesn’t much relate to that, if then also you like to refer it, so please follow the link;

As the name suggests, Server security will deals with the security concerns related to the server. So the SQL Server uses a 2-phase security authentication scheme. First, the user is authenticated to the server. As soon as user is inside the server, access can be granted to individual databases.

The Server can be secured with any of two modes:

  • SQL Server Authentication Mode
  • Windows Authentication Mode

Well, we will deal with both of these one by one, and see that what are the Pros and Cons:

SQL Server Authentication Mode:

Well, I think you would be very much familiar with this option while installing the SQL Server setup on your PC, i.e., to select either the Windows Authentication or Mixed Mode.

Windows Authentication Mode is for the Windows Authentication only, while the Mixed Mode is for both the Windows Authentication Mode and SQL Server User Authentication.

Therefore, the Security can be managed in the two steps:

1. Click on the Properties option of the database instance.

1_SQL_Server_Server_Security_Part1

2. Change the options if you want to:

2_SQL_Server_Server_Security_Part1

Well, you can also see the current login configuration of SQL Server on the machine with the stored procedure:

EXEC xp_loginconfig

The results can be seen as:

3_SQL_Server_Server_Security_Part1

It is an extended stored procedure which is being stored in the registry in the entry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MicrosoftSQLServer\<instance_name>\MSSQLServer\LoginMode

The above LoginMode = 1 for the Windows Authentication and LoginMode = 0 for the Mixed Mode.

Therefore, the authentication mode can be set in the two ways, i.e. either using a Management Studio or RegEdit.

Well, this was all about SQL Server Authentication Mode for this article-post, in the next article I would like to deal with Windows Authentication.

So Keep Tuned!

Hope you got it understood well 🙂

And also comments on this!!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

One Comment on “SQL Server – Server Security – Part 1”

Leave a Reply

Your email address will not be published. Required fields are marked *