Monthly Archives: March 2012

Building up to SQL Server Security

There are lots of things we do to be amazingly secure without even knowing it. We must learn about the security steps everyone else has taken before us so we don’t undo them. Uptime and usability can often interfere with sound security practices. The smart ones appreciate an access denied message once in a while.

I’m a fan of the distributed platform. Its a way of not putting all of your eggs in one basket.

5 rings of death fire

Every company has or should have an inner sanctum where the most precious of storage devices are held. In fact you should have two inner sanctums, one where the encrypted data is stored and one where the private encryption keys are stored. Separating duties is important so one person doesn’t run in godmode(sysadmin AND key admin).

What I want you to picture is your data as it sits right now. Consider the data in rings separated by red lines, the outer most ring might be the data that is cached in the clients browser(1). The secondary ring might be data that persists on the web server(2) such as a password in a config file or the non-persistent data such as in-memory compiled code.

At this point your 5 rings of death fire might start to look more like an onion. There are authentication requirements that force you out to a protected domain controller(2.5) before you can pass into the next layer of the database server(3). Once you authenticate to the database server there are authorization rules that grant you access to stored procs and views(4). Maybe then you have to reach out to a key to decrypt the inner most treasure(5). You might be picturing the inner sanctum as something that is small, but I would suggest you put your data warehouse in it.

The 5 rings of death fire is just one way of thinking about what you are trying to secure. These hurdles or red lines may have already been setup for you so don’t go knocking them down.

Physical security

Yup, still important even if you don’t own your cloud. Its mostly a joke with today’s VDI, VPN and RDP. Popping a guys eyeball out to pass the retina scanner was cool in the movies but unnecessary. We still need to lock down console access. If a local firewall service is disabled, the console access is the only way to enable it. Also, consider the piece of paper I know you have stored in your top desk drawer that has all of your passwords. All it takes is one crazy party for that to disappear.

Network Security

This is another low level security measure. Want to lock a hacker out, unplug your cat5. You won’t get much done but neither will they. It is very important to understand three things are needed to open up a connection, two IPs and a direction. One IP is the client app and the other IP has the listener. SQL Server listens by default on port 1433 but if you want to change the port you can, just use a comma in-between server and port when connecting. Telnet is the simplest program to check to see if you have a clear path to your listener or server. This is a step up from pinging because Windows turns ping (ICMP echo request) off by default. The direction is important because you can ping the client from your server but you can’t ping the server from your client.

Chances are you are not going to allow the internet to open up port 1433 connections to your database. If you are, you need to restrict the ability to do this using a whitelist and secure the traffic using certificates. Separation of duties is dictated in many compliance directives such as PCI. Two physical hardware firewalls, separately maintained (two people) is a requirement. Separation of applications and services on hosts is also a requirement.

Tools such as wireshark and netmon are powerful tools and should not be left on your server. You can prevent installs from non-administrators but if the weapon is already locked, loaded and key stuck in the case there is no need for administrator privileges. Use these tools to explain to your Java developers why you should use Kerberos instead of NTLM or SQL Authentication.

Windows Security

Patch patch patch patch patchity patch patch. Let me repeat that, patch your servers. Critical security updates that could potentially apply to your system is a must. Again the uptime nazis will have something to say about this and have a pretty good argument. They might say not to bother with patches that don’t apply but that is somewhat unrealistic to have Windows Admins investigate every patch given the workload. Sometimes the KBs are not particularly clear what is affected. My stance is to have your testing environments installing updates on a regular basis, daily perhaps or Wednesday’s after patch Tuesday’s. Production system should follow shortly after or once a month. In my three years I have seen 0, count it 0 windows patches that have broken any application let alone a Microsoft app. Don’t let anyone’s change paranoia stop you from patching.

Remember domain admins/enterprise admins/local admins have access to just about everything. 2008 stopped giving built-in\admins sysadmin but that was easily worked around with a psexec.exe -s call to sqlcmd.exe. 2012 is a little better by not granting the nt athority/system account sysadmin, however there is already ways around that out on the net


You may be able to open a connection to sql but can you login? You can sit down at this computer but the first thing you (should) see is a login prompt. What SQL has done quite well is integrate with Windows Active Directory security and use Kerberos. R2 didn’t quite make the cut for MSAs but 2012 does.

Windows Auth is great but makes an argument for physical security. The ceo’s and managers should definitely lock their computer when they are away because someone can sit down at a computer and use the token already generated to open a connection to sql and query away. No passwords needed. Even with that loophole Integrated Security is stronger than SQL Auth by a 1000 million percent. There are two protocols to choose for a login and one is still there but turned off by default. Like I said, don’t knock down those hurdles.

You should turn on failed and successful login logging. I overheard a friend say, “Nobody cares about the failed logins, its the successful ones we need to worry about.” Its intriguing but we need both to identify brute force attempts. Failed logins logging helps debugging as well. c2 audit logging will help keep a highly compliant record of access to objects.

Windows AD groups can be used to manage users as groups of people rather than individuals. Logins to SQL can be created from these groups which I highly recommend. If you want to do this you need a good AD auditing policy and a small number of domain admins. You need a trigger to alert you when users are added to sensitive AD groups. A regular peek at who’s in the group is not good enough. Some DBAs may not be comfortable with giving your AD admins access to the database but remember what I already covered about your domain admins already having access to everything.

1 Comment

Posted by on March 20, 2012 in Network Admin, SQL Admin


It sounds easy, graphing http errors over time

I have access to several fancy dancy monitoring tools. They alert and report quite well but I had a unique case where I needed to adjust a website monitor. I was getting alerts almost daily that the website was down but I would immediately check it out and it was operating just fine. The server logs showed no trace of the monitor request or any errors to point me in any particular direction.

I approached this issue with the assumption that is was either the inconsistent interwebz or a monitor false alarm. We have monitors that were successful that run via agents locally on the web server. That proves the server was functional, it just wasn’t receiving the traffic.

What I needed was a a tool that could check the site more frequently and allow me to adjust the timeout value. Sure, there are probably a lot of tools already, but why not give myself maximum flexibility and write one myself.

I realize it’s a little sloppy and needs some thread management to handle some timing but it did the trick. It’s a GUI with a few buttons but here is the code behind the form.

Imports System.Net
Imports System.Data.SqlClient
Public Class Form1

    Private Sub btnStart_Click(sender As System.Object, e As System.EventArgs) Handles btnStart.Click
        Timer1.Interval = Integer.Parse(txtInterval.Text)
        Timer1.Enabled = True
        System.IO.File.WriteAllText("ispchecklog.txt", "")
        btnStart.Enabled = False
    End Sub

    Private Sub btnStop_Click(sender As System.Object, e As System.EventArgs) Handles btnStop.Click
        Timer1.Enabled = False
        btnStart.Enabled = True
    End Sub

    Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
        CreateCommand("INSERT INTO [ISPCheck].[dbo].[log] ([stamp] ,[rc], ) VALUES ('" & DateTime.Now.ToString("yyyyMMdd hh:mm:ss") & "','" & WRequest(txtURL.Text) & "','" & System.Environment.MachineName & "' );")
    End Sub

    Function WRequest(ByVal URL As String) As Integer
        Dim responseData As String = ""
            Dim hwrequest As Net.HttpWebRequest = Net.WebRequest.Create(URL)
            hwrequest.Accept = "*/*"
            hwrequest.AllowAutoRedirect = True
            hwrequest.UserAgent = "http_requester/0.1"
            hwrequest.Timeout = Integer.Parse(txtTimeout.Text)
            hwrequest.Method = "GET"

            Dim proxy As IWebProxy = WebRequest.GetSystemWebProxy()
            proxy.Credentials = CredentialCache.DefaultCredentials
            hwrequest.Proxy = proxy

            Dim hwresponse As Net.HttpWebResponse = hwrequest.GetResponse()
            If hwresponse.StatusCode = Net.HttpStatusCode.OK Then
                responseData = 200
            End If
        Catch e As Exception
            System.IO.File.AppendAllText("log.txt", DateTime.Now.ToString("yyyyMMdd hhmmss") & e.ToString & System.Environment.NewLine)
            responseData = 0
        End Try

        Return responseData
    End Function

    Public Sub CreateCommand(ByVal queryString As String)
            Using connection As New SqlConnection(txtConnstr.Text)
                Dim command As New SqlCommand(queryString, connection)
            End Using
        Catch ex As Exception
            System.IO.File.AppendAllText("log.txt", DateTime.Now.ToString("yyyyMMdd hhmmss") & ex.ToString & System.Environment.NewLine)
        End Try
    End Sub

End Class

What I got back was a lot of alerts in a table with their time and http return code where 0 was a timeout. I had this test running on several computers so the time values were not consistant. I thought they would line up to show some “outages” but I couldn’t tell from just looking at the table. I wanted to see if they were clustered together so I needed to see them on a graph. No matter how I sliced it I couldn’t get it to look good. Then I realized what I needed was a time dimension, and then to count the errors within certain time slices. This query will group the events into 5 minute intervals. Also important, it will give me the intervals that don’t have any alerts so I can easily produce a graph over time.

dateadd(minute,(datediff(minute,0,stamp)/5)*5,0) as interval,
MIN(rc) as min_rc,
COUNT(case when rc = 0 then 1 end) as 'number of occurances'
from ispcheck.dbo.log
group by dateadd(minute,(datediff(minute,0,stamp)/5)*5,0)

This link got me close to the query I needed. I found quite a few that didn’t really do the trick at all before I located this forum post.

Leave a comment

Posted by on March 18, 2012 in Network Admin, SQL Dev


Tags: ,

back to t-sql basics (2 of 2)

So you want to make changes to the database? Well that has all sorts of implications. My experience with database change is only with small applications that I have written myself. The other experience I have is troubleshooting a few ISV applications that require me to delete or update erroneous records.

This lack of experience has driven me. It should be a requirement of anyone administering SQL to know SQL. The DMVs are great way for a sysadmin to get started querying. However, you don’t write to these objects. So the first time you need to preform an update or delete there’s a bit of voodoo involved.

What I have started doing is collecting useful information using some of Allen White’s DIY baseline and monitoring powershell scripts. ( This has started giving me at least a cursory knowledge of IUDs by loading this data into other tables that I need to maintain. Also, cleaning up data that is old is a must. But again this small-time development is mostly a refresher for me.

I’ve gotten great experience at the enterprise sysadmin role but I seriously lack the production development DBAs skills. I hope I have a chance to learn about schema changes on busy databases before I have to make any. Also, I would like to be able to offer more assistance for the database design problems. For now, I don’t stay awake at night worrying about a missing where clause on a delete or an improperly nested update query but maybe someday it will.

There are two remaining chapters on concurrency and programming that I might be covering with some notes later. This book has my highest recommendation. Without further ado, I have taken these notes on my reading of: Microsoft SQL Server 2008 T-SQL Fundamentals by Itzik Ben-Gan.

(1, p238)
Insert into values has changed in 2008. You can now include multiple values (1,1,1),(2,2,2),(3,3,3).

Insert into select allows you to insert records from one result set into a table. This is called table value constructor.

Insert exec works like insert select but allows you to insert the results of a sp.
(1, p241)
Select into is non-standard sql. It allows for table creation on the fly.

Bulk insert is a very fast method of loading from a flat file.
(1, p242)

Bulk insert from ‘c:\inputfile.csv’ with
(fieldterminator = ‘,’,
Rowterminator = ‘\n’)

Identity allows for auto incrementing a field in a table. Do not specify this column on the insert and based on the seed and increment the value will increase. IDENTITY(1,1) means start at 1 and increment by 1. This is not the same as a sequence.

SCOPE_IDENTITY() will return the last identity used within the current scope. Identities will increment on failed inserts. Deleted records will create gaps in identities. Ident_current() will ignore session and give you the last identity.

(1, p247)
SQL only has two commands for deleting data, delete and the non-standard truncate. Deleting whole objects such as tables can be done with the DROP command.
Deletes based on a join are non-standard, use deletes with subqueries instead.
2008 allows for += where previous versions don’t allow those compound operators
Sql does all at once operations. Consider col1 = 20 and col2 = 10

Update testable
Set col1 = col2, col2 = col1;

This query would swap col1 and col2 where in most languages the values would end up being the same.
Just like deletes, updates can be based on a join but it is non-standard. Use subqueries

Update testable
set col1 += .05
where exists
(select * from testtable2 as tt2
where tt2.col1 = testable.col1
and id = 1)

(1, p254)
Assignment Update = new to allow creating your own sequences
MERGE; allows for IUDs with logic. Its also new to 2008 and is mostly ansi standard.

(1, p259)
SQL allows the use of CTEs to modify data… whoa…
-can be useful to select data before modifying it
-can use a table expression to select TOP and orderby… then run your update

(1, p266)

Delete from testable
where col1 < ‘20010101’

This will return col1 and col2 for all the records that were deleted.

1. Ben-Gan, I. (2009). Microsoft SQL Server 2008 T-SQL Fundamentals. Redmond, Washington: Microsoft Press.

Leave a comment

Posted by on March 11, 2012 in SQL Dev


meme Monday: DBA done list

This is my first participation in meme Monday.

SQL isn’t all I do but it is the main thing I focus on. I do stick on the server side and don’t get much into the database development side. So here is my list:

Index maintenance
Stats maintenance
SQL Installs
SQL Uninstalls
Database migrations
grant access
Windows patches
SQL patches
tune queries
talk to isv support
tune networking settings
closing alerts
overriding alerts
reporting on backups
testing restores
sending trace results to devs
report on used space
report on space projections
SAN monitoring
Racking servers
tracing cables that don’t work
evaluate default policies and try to fix failures
Hassle people to upgrade their crapware that runs on sql 2000
research open source SQLs for ISV apps (mysql, PostgreSQL)

I spend a decent amount of time asking for stuff
New vm requests
New hardware requests
training requests
book requests
gimme space requests

Leave a comment

Posted by on March 5, 2012 in SQL Admin