Today, February 10, 2015 is TSQL

Tuesday, no.63! (Click on the highlighted link for the Invitation.)

 Hosted this month, by Student of SQL,

aka on twitter @sqlstudent144,

and maintains his SQLStudies.com blog,

Kenneth Fisher. Ken asks us a very pertinent question that makes up our latest

T-SQL Tuesday topic: How do you manage security?  Answer: Very carefully! 🙂

So, as Ken

correctly notes, all of us DBAs, regardless of specialty, will have to deal

with security in our database careers, and must be concerned about securing SQL

Server, against outside forces, whether accidental, or foul-play, natural or

man-made.  One of the most important

things we have to do as a DBA, is to ensure the security of our SQL Server

infrastructure, so data doesn’t go missing or that there is a potential breach

of proprietary data.  We also must be

certain to lock down and reduce the surface area of attack, as well as get

those backups off-site for safety, and ensure data recovery in the event of

disaster.  A most serious breach can

bring a SQL Server to its knees, result in denial of service (DOS) and affect a

business’s bottom line.

Security is

indeed a broad topic, and I discuss several aspects of SQL Server security in

my upcoming book HealthySQL.  For T-SQL

Tuesday purposes, I will mention a few things about security here, and provide

a couple of tips for you.

Although it

is often recommended to have Windows Authentication only, this is not, for many

reasons, always possible. Microsoft SQL Server allows mixed mode where a SQL

Server Authenticated account and password is enforced by the same API and

protocol that enforces windows login policies.

Strong

password policies require and check for minimum password length, proper

character combinations, and passwords that are regularly changed every so often.

This practice leads to more secure passwords and makes security breaches of

database servers much more preventable.

SQL Server supports

password complexity and password expiration, which allows for more secure

password policies.  Therefore, database

security is now enhanced by the same windows password policies that can also be

applied to SQL Server password policies. The password enforcement options

available on the create new login screen, under SQL Server Authentication, are

shown in the below screenshot:

As you can see there are (3) options

outlined:

•  Enforce

  password policy;

•  Enforce

  password expiration;

•  User

  must change password at next login.

So, once

you create your SQL Authenticated login, and of course map the login to a

database user, grant access and roles, how can you keep track of this login and

its policies?  As of SQL Server 2008,

through the latest version, you can use the LOGINPROPERTY

and the sys.sql_logins

system view to keep track of and get information about the login policy

settings. 

With the

following script, quickly answer various inquiries about the status of the login,

and see if the login is:

o   Disabled

o   Locked

o   Expired

o   Must User Change Password at Next

Login

In addition, the script will also tell you when the

password was last reset, the last failed login date & time, the date &

time when it was locked out, and provide a count of failed login attempts. Moreover,

you will also be able to get the number of days left until the password expires!

 If you’re already thinking what I’m

thinking, you can use this info to set up alerts and notification.  You can even create a user notification

warning them, for example, that their password is expiring in X amount of days.

The only

caveat for this script, is in order to return ALL of the above info, you must

in fact enable the enforce password and policy expiration options – which is

why I highlighted them in the pic above J

So, here

is my SQLLoginSecurity script of the day:

SELECT
name,
CASE Is_disabled
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'Unknown'
END as IsLoginDisabled
,CASE LOGINPROPERTY(name, 'IsLocked')
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'Unknown'
END as IsAccountLocked,
CASE LOGINPROPERTY(name, 'IsExpired')
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'Unknown'
END as IsPasswordExpired,
CASE LOGINPROPERTY(name, 'IsMustChange')
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'Unknown'
END as MustChangePasswordOnNextLogin,
LOGINPROPERTY(name, 'PasswordLastSetTime') as PasswordLastSetDate,
LOGINPROPERTY(name, 'BadPasswordCount') as CountOfFailedLoginAttempts,
LOGINPROPERTY(name, 'BadPasswordTime') as LastFailedLoginTime,
LOGINPROPERTY(name, 'LockoutTime') as LoginLockedOutDateTime,
LOGINPROPERTY(name, 'DaysUntilExpiration') as 'NoDaysUntilthePasswordExpires'
From sys.sql_logins
order by name

     Thinking about joining the T-SQL Tuesday Blog Party?  Don’t think too long – today is Tuesday! But

if you are, please follow these few simple guidelines:

1. Your T-SQL Tuesday post needs to go live between 00:00 GMT Tuesday

   February 10, 2015 and 00:00 GMT Tuesday February 10, 2015.

2. Your blog post needs to contain the T-SQL Tuesday logo

   image on this post, and it should link back to Ken’s

   post.

3. Use

   trackbacks or post a comment to Ken’s

   post with a URL link to your post.

 **

An optional but preferred step, please tweet about your post using the #TSQL2sday twitter hashtag.

 T-SQL

Tuesday is a blog party started by Adam Machanic (b/t) just over five

years ago. Finally, if you would like to get on the list and sign up to host a

blog party of your own go ahead and ping Adam with your favorite topic!

 Always

maintain a Healthy SQL Server environment, and know that Security is a big part

of HealthySQL.  And

remember, we’re serious about security, your SQL Server security! {End PSA}  🙂

 Follow

me on twitter @Pearlknows