Category Archives: Security

Dastardly database (and password) hiding

I’ve been recently going through the first part of one of Redgate’s excellent free ebooks “The Red Gate Guide to SQL Server Team-based Development” and was rather amused when I came across the section talking about creating what they term “shape” tables. Essentially what this means is to use the shape ascii codes in order to name a table. I remember a very very long time ago when I was still (almost) in short trousers reading an article suggesting that user passwords should incorporate various invisible ascii codes (such as escape codes) in their respective passwords, since this would render dictionary password cracking tools pretty much useless. It would also help if someone was looking over your shoulder – yes they have just observed you type MyP@ssw0rd but they didn’t quite catch that ascii code you also typed did they?!

Anyway, all this got me thinking (yes sometimes this does happen) “I wonder if databases could be named using ascii/extended ascii for those invisible codes?”

For those of you who are not aware of how to type out an ascii code it is fairly simple. All you need to do is hold down the ALT key whilst typing out the numeric decimal code and releasing the ALT key. Depending upon what the code and the text editor you are in, the code may or may not cause a character to be input. For instance typing ALT+032 will cause a normal empty space to be input to your editor.

After much trial, error and disappointment I was not having much success with invisible codes although I did find one immediate surprise -namely that you can call databases with no names! I’m sure I must have read this somewhere but my conscious self does not recall it. One interesting thing about this, is that irrespective of how many spaces you put between your squared brackets it is always interpreted as the same name so essentially you can only create one database using spaces (or ascii 032).

But much toying later I finally I had my eureka moment! I found that invisible ascii code 255 does generate an invisible character which isn’t interpreted as an empty space. Even more interesting was that unlike ascii 032, multiple codes were being taken as a unique database name meaning that multiple databases could be created each with an apparent blank name – a good one for scaring your junior dbas :). So is there any use for doing this? Actually I can think of a couple but would be interested in any of your suggestions. I may at some stage follow this post up to go through a few of them.

I eventually decided to return back to the subject of user passwords. I again wondered if it was possible to use my magic ascii code of 255 when creating a SQL login. Yet again this took and the “empty space” was in fact being treated as a real character. In other words I couldn’t substitute it for a SPACE. This is a very good thing because it means SQL login passwords can made be even more complex be using a combination of this code and real spaces. Actually I have a suspicion that you could probably use ascii 255 for naming any SQL Server object such as tables, columns, functions etc. or even use it in your data. I’d be very interested to here about any tests you perform so please get in touch if you do.

I can envisage several really interesting security uses of using this technique of using the hidden character within your database name and may at some point test these scenarios out, but I would be irresponsible if I didn’t warn you to be careful. Firstly I do not know of the official stance on using this ascii code for object naming and it is just possible that future versions may break the way it currently works, although I think this is unlikely. Secondly because this code is invisible you might get into a situation where you cannot remember the sequence yourself particularly in the case of an old legacy database – although I cant currently think of a situation where you would not be able to recover from this but maybe further testing using certain scenarios and SQL technologies may reveal something and of course if I do find something…I’ll let you know!

Use protection to stay available

One of the biggest worries for us grumpy DBAs are the risks which are posed to us due to our reliance on other teams, specialists and technologies in order to maintain a working and efficient SQL Server infrastructure. This was one of the key concepts when I put together my Thinking outside the Box talk for SQLBits 7, and insist that we need to understand, get involved and help communicate best practices to our colleagues and fellow IT professionals.

An often overlooked area of our SQL Server infrastructure is the lack of protection afforded to the machine and service accounts that our instances and Clusters are using, and the dangers posed to these environments because of this is enormous.

Particularly in the case of SQL Clusters, should a well meaning Windows Administrator accidentally reset or remove the Cluster Group network names, you have a potentially very big problem to deal with. On deletion of the account you still have the possibility of recovering this account from tombstone using a third party tool (more on this another time) but if the account is removed, the first you will know of it is when your SQL Failover Cluster fails and refuses to start on any other nodes in your cluster.

New to Windows 2008 is the ability to protect your accounts and machine names from accidental deletion and it is very simple to do. Firstly as the DBA you should list all the objects that you wish to protect from your Cluster names, SQL Failover Cluster names (aka virtual servers), Windows network names (you should do this as well for all of those servers running standalone SQL servers) and all Service Accounts used within your Clusters and SQL instances. In short, for any object you DONT WANT DELETED!

Windows 2003 users can refer to this excellent article “Protect Objects in Windows Server 2003 Active Directory from Accidental Deletion” instead.

Once you have compiled your list all you need to do is get your friendly Windows Admin to load up the Active Directory Users and Computers snap in and for each object look at it’s properties.

On the Object tab, ensure that the “Protect object from accidental deletion” checkbox is ticked.

A little bit of forward planning in this area could save you and your fellow professionals a world of pain. Be sure that you think ahead, use protection and maintain your availability.

Stub out your bad practices when deploying object promotions

When a script is deployed in your production environment, how is it deployed? Are the scripts written to account for the object already existing and if so what actions are taken? But what if the object doesn’t exist, what then? All too often in these scenarios the usual style of deployment scripts I see being provided to database teams first detect the existence of said object and then drop it (should it exist). This then leaves the way clear for the script to perform its create statement. is there really anything wrong with this approach? Well yes, actually there is.

Every time an object is dropped from a database you are also removing database user permissions to it. This sounds (and ought to be) blatantly obvious, but from my experience it doesn’t really seem to be the case or perhaps is thought to be not too important. In the cases where a developer is aware of what is happening, they tend to add extra DCL to their promotional script so that security is added back in. Obviously from a DBA perspective, having the security of your database and objects open to manipulation (albeit through a legitimate route) is not a good thing and therefore active usage of DCL should be discouraged or forbidden for standard promotions.

devs vs dba

Developers VS DBA

For those developers who are not aware that dropping an object loses the permissions, I believe they must think that the permissions somehow remain intact for the database user itself so that when an object is recreated the permissions would become relevant again. THIS IS NOT THE CASE! Remember, when dropping and recreating an object, it results in a new objectid and so even if the permission set did remain on the database user they would not point to this new object anyway.

A different approach to your scripting is needed. Logically you must ensure that if an object exists then it is ALTERed and if and only if the object does not exist should you issue a CREATE. At no time whatsoever should a DROP ever be issued unless of course a complete and persistent removal of an object is the desired consequence.

There are a couple of hurdles to overcome in order to achieve this. The first is that the ALTER and CREATE statements must be the first statements of a batch and secondly logical IF…ELSE constructs and GOTO operators cannot span batches, therefore how can it be possible to check to see if the required object already exists and to take the relevant action depending upon that result?

tearing out your hair

tearing out your hair

The way around this issue is to have the object modification script to always flow sequentially and once any logical branching has completed to continue along the same path. Also the object change script should always perform an ALTER statement, and this can occur post GO batch separator. This gets around most of the problems but we haven’t yet accounted for the possibility that the object may not exist. Since we are always performing an ALTER for the change script, all we simply have to worry about is ensuring that we create the object if it doesn’t already exist. In this instance we still face the issue regarding the CREATE statement requiring its own batch, but the problem here is that in order to know whether a CREATE is necessary, a logical test needs to be performed, and the solution to this is not so obvious since the logical test means that the CREATE would not be the first line in a batch!

The solution to this last problem is actually quite simple. We can execute the CREATE batch by utilizing the EXEC statement, and this is great since we can now nest this within a logical existence test. This solution meets all of our requirements. Permissions and objectids are retained, and the script is very simple to maintain. Only the ALTER code segment would need to be updated for future code revisions.

USE dbaadmin
IF OBJECT_ID (N'usp_killspids') IS NULL
    EXEC ('
    CREATE PROCEDURE usp_killspids AS
    SELECT ''This is a stub procedure, implementation of it will
     be created by an ALTER statement''
    PRINT 'Procedure usp_killspids does not exists, creating a
     stub procedure before executing create script...'

ALTER PROC [dbo].[usp_killspids]


Hope this helps?

Make your life easier write an app make RAD work for you

Quite often I come across scenarios where you know what you need to do, you know how to do them, but know the way to do it will be very repetitive in SSMS. In these instances the best way to overcome them is to create a few queries and join them up into a little applet.

One such example I had recently was that we wanted a way to very quickly check several servers for privileged database and server role membership.

So in simple terms I needed to see every login that was a member of any of the server roles and in addition I needed to see any database user that was a member of database owners database role or the owner of the database directly.

This is actually something that you would have thought should be pretty easy to do in SSMS, but this is not really the case. The GUI requires that each principal is looked at one at a time in turn viewing the membership of each, and it is quite labourious and infuriating when there are many to look at.

The solution is relatively simple to code in T-SQL using the Dynamic Management Objects but running this script against more than a couple of servers in an adhoc fashion really could be simplified. In my particular situation I was asked to task a junior (non SQL operational) DBA with compiling this information and therefore the obvious solution to was to make this as easy as possible for them. It took me around 15 minutes or less to write a front end GUI which allows the user to enter the server name in a text box and both queries are ran against the server in question and the results are presented in a MDI child split pane window.

Maybe one day SSMS will provide more extensible functionality, but for now small applets in certain situations work well for me.

Loading CHM files from external media

Another very quick but useful tip that I first had to do several years back and most recently a few days ago was to have to tell Windows to ignore the usual security constraints and allow other data sources. Otherwise when you attempt to load e-books from these data sources, you will see the following:-


Searching Active Directory membership …when a GUI just ain’t enough

Bit of a quickie here but a goody. Nothing really new either but since I’ve just spent the last 10 minutes piecing the command together (the last time around 6 years ago) I might as well make a record of it.

So the problem is that you have a very long list of users and groups within an Active Directory group and screen shots don’t cut the mustard any more. The solution is to use the dsquery command line tool.

The first query simply outputs all group members in an Active Directory group and the second query outputs all the group membership belonging to an Active Directory user. Easy, simple, quick and efficient.

login failures for windows based authentication what does that error mean

Have you ever come across the following login failure within the SQL Server error logs? “Login failed for user ”. (Note the empty user name). This is all a bit unusual if you think about it. Since the user attempting the authentication should theoretically have already been authenticated to the windows domain, you would have expected that this user-name would be displayed here in the authentication failure.

One reason I have seen this happen is when a user has already authenticated to Windows and then changes their password – which is a perfectly reasonable thing to do. It seems that logging off and back on again resolves this issue, but you would think Windows would have updated its SID both remotely and locally! It is unclear to me why the warning does not display the user account in this situation, but most probably has something to do with the system attempting to reverse map the SID to a user name. Since the SID is probably out of date it is possible that windows cannot find it in its database.

In conclusion though, I believe these authentication errors can most probably be ignored since they are not necessarily someone trying to gain unauthorized access to the SQL Server, just someone who has not updated their SID.