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.

2. Change the options if you want to:

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:

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