SQL Server Security Basics | What is Authentication?

By definition Authentication means the process of verifying the identity of a user or process. If a user wants to talk to the database, SQL Server asks “Who you are?”, and authenticates you. There are three types of authentication modes available:

  • Windows Authentication
  • SQL Server Authentication
  • Azure Active Directory

Windows Authentication

  • This is the default authentication mode and the more secure as compared to SQL Server Authentication.
  • Microsoft BOL recommends to use Windows Authentication over SQL Server Authentication.
  • This mode is available both on SQL Server running on-premises and Azure Virtual Machine.
  • It uses Kerberos security protocol.
  • Connection made under this mode is also called “Trusted Connection” as SQL Server trusts Windows credentials.
  • Has additional password policy, such as strong password validation, support for account lock and password expiration.

SQL Server Authentication

  • Logins here are validated which are created and managed by SQL Server.
  • Unlike Windows Authentication, user should provide credentials every time while connecting to SQL Server.
  • There are few (optional) password policies also available.
  • This mode can be used where there is a requirement to support mixed operating system for applications and users cannot be validated using Windows domain.
  • Can be useful with web-applications where users have the provision to create their own identities.
  • It does not use Kerberos security protocol, and there is also a risk for applications that connect automatically with the SQL Server may save the password in file in clear text.

Azure Active Directory

  • This authentication mechanism validates an identity based on Azure Active Directory (Azure AD).
  • It supports token-based authentication, ADFS (domain federation) and/or built-in vanilla authentication without domain synchronization.
  • It can also support mechanism of password rotation in a single place.
  • Allows management of identities centrally (Central ID), which helps in simplifying user and permission management.

I am going to write a series of blogs on security basics, this one is first one in the row. Stay tuned.

References:

Choosing Authentication Mode

Azure AD Authentication

Advertisements

SQL Server (vNext) on Linux – CTP 1.0

linuxlove

Last month November 16th, 2016, Microsoft announced its first Community Technology Preview of next release of SQL Server, called SQL Server vNext. This release will run not only on Windows but also no Linux, Docker, or macOS (via Docker).

Here is the download link for SQL Server vNext CTP 1.0

Sneak peak: New features and enhancements

Database Engine

  • Addition of new compatibility level 140
  • Improvement in incremental statistics update threshold (available through new database COMPATIBILITY_LEVEL 140)
  • Addition of new DMVs:  sys.dm_exec_query_statistics_xml (return live query plan and execution statistics for the running batch), sys.dm_os_host_info (provide operating system information for both Windows and Linux)
  • Many performance and language enhancements to In-Memory tables:
    • Support for more than 8 indexes
    • Support for sp_spaceused, sp_rename, CASE statement, TOP (N) WITH TIES
  • Clustered Columnstore Indexes now support LOB columns (nvarchar(max), varchar(max), varbinary(max)).
  • New function: STRING_AGG()
  • Database roles are created with R Services for managing permissions associated with packages
  • Addition for new Japanese Collation

R Services

  • Microsoft R Server and SQL Server R Services provide a variety of new features to enhance integration of R with SQL Server and the Microsoft BI stack

Integration Services (SSIS)

  • Support Scale Out of SSIS – easier to run SSIS package on multiple machines
  • Support for Microsoft Dynamics Online Resources – connection to Microsoft Dynamics AX Online and Microsoft Dynamics CRM Online thru OData source and connection manager

Useful Links

 

If backups are taken in silence can a recovery still take place?

SQL Studies

T-SQL Tuesday My friend Andy Mallon (b/t) is hosting T-SQL Tuesday this month. In case you aren’t aware T-SQL Tuesday is a blog party started by Adam Machanic (b/t) almost 7 years ago. Each month someone selects a topic and hosts the “party”. Then whoever is interested posts a blog on that topic. It can be a great way to get a good grounding on a subject as seen by a bunch of different bloggers or start your own blog.

Regardless, Andy’s topic this month is We’re still dealing with the same problems. The idea is that we’ve been dealing with the same problems for 20 years or more. Of course this being T-SQL Tuesday he wants a database spin.

So let’s talk backups.

We take backups for multiple reasons. One of the big reasons is to help us fix day to day…

View original post 219 more words

SSMS’s Clipboard Manager

SQL Studies

The other day Richie Rump (b/t) mentioned something called a clipboard manager on twitter. I’ll admit I had to ask what exactly they meant, but once it was described to me I realized it was something I’d wanted off and on for years. Basically it’s a tool that stores multiple copies in an extended clipboard. So you can copy several pieces of text over time and then paste the one you want. Justin Dearing (b/t) and Richie mentioned a Clipboard Manager called Ditto Clipboard Manager. Kendal Van Dyke (b/t) however mentioned one built into SSMS! Now, it only covers what’s currently in the clipboard and what’s been copied in SSMS but since I spend most of my time in SSMS (I even frequently use it as a text editor) that’s just fine. Once you’ve copied several…

View original post 28 more words

Common mistake using try/catch constructs

As per BOL, A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection. This is a common thing which a developer ignores and get into a trap. Let’s try to understand with an example code.
Here is a sample code, where exception raised by select statement executed in try block will never be caught by catch block:


create table test_tbl
 (id int);
go

begin try
 select *
 from test_t; -- mistakenly typed wrong table name
end try

begin catch
 print 'In catch block - exception handled!';
end catch

-- #Output
-- Msg 208, Level 16, State 1, Line 2
-- Invalid object name 'test_t'.

Here is another example, showing the correct usage of try/catch construct – using divide-by-zero error.

begin try
    select 1/0; -- divide by zero error
end try

begin catch
    print 'In catch block - exception handled!';
end catch 

-- #Output
-- (0 row(s) affected)
-- In catch block - exception handled!

 

Please refer below link for more details:  Books On-Line

Happy Learning!