Truth is, Trace Flags are NOT really a dark art. But one thing I’ve noticed about the web’s ‘coverage’ of Trace Flags is that there are very few demonstrations of how to set Trace Flags that explain the process entirely, or in ‘idiot-proof’ steps.

Trace Flags in Review

If you’re not familiar with SQL Server Trace Flags, the the simplest way to define them is to say that they’re optional arguments, switches, or parameters that you can apply to SQL Server to make behavioral changes to the way the engine, certain features, or operations, act and behave.

Official documentation for SQL Server Trace Flags can be found in Books Online.

What that documentation fails to cover however really comes down to two things:

1) The use of Trace Flags is only something you should undertake with great care.  You can easily destroy a production system by playing around with them. In other words, they’re a sharp stick – and sharp sticks are great for some things. They can also poke your eye out if you’re not careful.

2) It doesn’t really describe HOW you go about adding a Trace Flag during startup – though it mentions that as an option.

Trace Flag Scope

By default, most trace flags that you set using DBCC TRACEON() will stay operation, or within scope, until the server reboots or you run DBCC TRACEOFF().

Therefore, if you’d always like a Trace Flag to be on, you’ll need to set it up as a startup parameter – meaning that when the SQL Server Service starts up, it will then apply the flag in question as part of the current operating or working environment. (Of course, you can still turn off most Trace Flags that were initialized at startup by just using DBCC TRACEOFF() if you need to temporarily turn them off, or if you’d like to turn them off and don’t want to restart SQL Server. More on that in a second.)

Setting Trace Flags as a Startup Option

To be fair, Books Online does detail how to set trace flags. (And maybe that’s why I’ve not found this covered too much on other sites/blogs when I’ve looked for instructions to send clients on how to make these changes.)

Specifics for changing/setting startup options are found here.

And there’s even a link to more specific instructions on configuring startup options as well.

Ironically, I didn’t know of either of these two links until I started writing this post. Which makes me a bit lame.

So, I’ll redeem myself a bit by providing some screen shots, and a concrete example.

Suppose, for example, you’ve got a system where you want to suppress successful backup messages. In many environments this wouldn’t make sense. But if you’ve got a multi-tenant server where there are lots of databases (say 5-20) and you’re doing regular log file backups every 15 minutes or so in keeping with best practices, then you’ll be spamming huge numbers of success messages into your Windows Event Logs and SQL Server Logs. As such, rather than letting those messages turn into noise that might distract you from noticing problems/errors, TRACE FLAG 3226 starts to make sense.

If you just want to temporarily suppress those messages, you can fire off DBCC TRACEON(3226) and those messages are suppressed – until the server reboots. And then they’ll be back.

If you want to make that flag/option ‘stick’ across reboots, then you need to add it as a startup parameter.

1. Fire up SQL Server Configuration Manager by going to Start > Programs > Microsoft SQL Server (2005/2008) > Configuration Tools > SQL Server Configuration Manager.

2. Select SQL Server Services, then right click on SQL Server (MSSQLSERVER) and select the Properties menu option.

3. In the Properties window, click on the Advanced tab, and then find the Startup Parameters option. Click the little down-arrow next to it and you’ll have more area to work in.

4. This is where you add any startup parameters you want to add – including trace flags.

In this case, since I want to add Trace Flag 3226, here’s what the startup parameters would look like.

BEFORE:
-dD:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eD:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lD:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

AFTER:
-dD:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eD:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lD:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-T3226

Note that to add the Trace Flag, I had to ‘terminate’ the previous options with the addition of a semi-colon. Then I was able to add the –T flag and the desired trace flag. 

As per Books Online, make sure you use the UPPER CAST “T” to set trace flags. Lower-case “t” will work – but implies that you’re setting flags allowed by SQL Server Support Engineers – which implies lots of ugly potential that you do NOT want.

Likewise, if you want/need to add multiple trace flags, then you just keep appending them like so:
;-T3226; –Txxxx; –Txxxx

Again though, just remember that Trace Flags can be a lot like juggling flaming, loaded, sawed-off, shotguns.

And, of course, in order to make your startup options ‘stick’, you’ll have to restart SQL Server. You can do this immediately by stopping/starting the service as per the screenshot below (and if you’re prompted to stop SQL Server Agent – go ahead and do that; just remember to RESTART it after you bring SQL Server back online):

Best Practices For Turning Trace Flags On/Off

Restarting your SQL Server just because you want/need to put a Trace Flag into play long term can often be a pain. (The same goes for cases where you’ve got a trace flag installed, and want to remove it.)

Therefore, what I’ve found that works really well when I’ve made sure that I want to set a trace-flag long term is to:

a) Go in and Modify the Startup Parameters with the needed trace-flag as specified above.

b) I don’t restart the SQL Server Service.

c) Instead, I use DBCC TRACEON(####, -1) – where #### is the id of the Trace Flag that I set in step 1 and –1 is the switch/option that pushes this trace flag global.

This process works well as it kicks the Trace Flag into play immediately, and the next time the server is normally rebooted (i.e. service packs, hardware swap-out/swap-up, whatever) the startup parameters will kick in and the trace flag will ‘persist’ in scope across restarts and go into active service.

The same approach works when I want/need to remove a trace flag as well. I just nuke it using DBCC TRACEOFF(####, -1), and then remove the corresponding startup parameters.

UPDATE: Thanks to Reader/Visitor “Developer Seer” who pointed out that I forgot to put the –1 switch into my example in step c above. This is the key/trick to getting your Trace Flags to become GLOBAL in scope – or to make them persistent.

A common need when working with SQL CLR assemblies (or projects) is to add a reference to a third-party (or external) assembly. Interestingly enough though (at least as far as I can tell), the steps for doing this are hardly ever documented.

So this blog post will fix that – by documenting just how you go about adding External References in Visual Studio to SQL Server CLR projects or assemblies.

Danger

In my recent webcast: Introduction to SQL CLR Extensibility, I was hoping to cover exactly how to do this. I was also going to (and partially did) throw out a few caveats when doing this.

Specifically: don’t go crazy adding external references. Good code IS reusable code – so good developers may occasionally run into the need to drop of ‘core’ logic into their CLR assemblies. On the other hand, lots of complex business logic just isn’t going to find a good home in your SQL Server’s memory-space; bundling up tons of business rules and the likes into SQL CLR assemblies (or as referenced assemblies) is a great way to bloat your CLR memory usage and, more importantly, will make versioning your code PAINFUL if you start to abuse this power.

Setting the Stage

For this tutorial, I’m going to assume that you’ve already GOT a CLR project/assembly that you’ve already deployed successfully to SQL Server – and that you’ve got it running as needed (minus the external references). If you don’t have that, then watch the webcast from the link provided up above, and it will show you how to get to this ‘assumed’ point.

To that end, if you’re an experience .NET Developer, your first instinct is to right-click on the References folder in Solution Explorer within your solution, and click on the Add Reference option. That in turn brings up the following:

The only problem, of course, is that it doesn’t have any ‘browse’ options or tabs. It does let you reference other projects – but even that has some serious limitations too – to the point where even if you were to add your existing ‘solutions’ as projects and try to build them and reference them, you won’t be able to do so.

What’s Going On?

The key to adding external references to a SQL CLR project is understanding that the ‘Add References’ dialog isn’t showing for what’s on YOUR development machine. Instead, it’s polling the SQL Server you’ve CONNECTED TO as part of your project. So, if you’re a developer you’re used to Visual Studio letting you add references to projects and DLLs that exist on YOUR workstation. Then, when you deploy or publish your code, those references are bundled along with your code and copied out to the destination.

SQL CLR projects DO NOT work that way. Instead, they ONLY let you reference assemblies that are already located on the server and which have already been REFERENCED with the server.

Once you know that, the rest is easy.

Adding Reference Assemblies to Your SQL Server

So, since you can only add ‘registered’ assemblies to your projects, the next question is how do you do that?

In this tutorial, I’m going to start with a brand NEW assembly. To do that I’m going to do the following:

Open Up a Brand New Instance of Visual Studio (I’ve already got my existing Solution open).

Create a New Project.

I’m going to Specify the Visual C# Type, and specify that I want to create a Class Library:

I’m going to name this Class Library ‘CoreLogic’:

Then, in that Class Library, I’m just going to add in some ‘core’ logic that I want to be able to ‘consume’ from my referencing assembly.

In this case, I’m just going to use some patently lame logic – which flips a string around (i.e. it turns ‘string’ into ‘gnirts’):

Then, at that point, I’m going to BUILD my ‘CoreLogic’ project, and then copy the CoreLogic.dll that came from the \bin\Debug\CoreLogic.dll folder within my project out to a location on my server.

From here I can then register the ‘CoreLogic.dll’ on my server – and it will then be available as a potential reference from within my other solution.

To register my assembly, I just need to use CREATE ASSEMBLY as follows:

And note that I’m creating this assembly in the DATABASE that I’ve connected to in my SQL CLR project (i.e., the project where I want to be able to add the external reference).

Full Circle – Adding the Reference to your CLR Project

Now that I’ve created the assembly up on my SQL Server, if I go back to the original SQL CLR project where I want to be able to add a reference to my ‘Core Logic’:

I’ll see the reference to CoreLogic show up if I again right click on the References node and select Add Reference:

From here I’m able to then add a using statement and so on – just as you would expect with a ‘normal’ .NET project.

Troubleshooting

Depending upon how you’re deploying your SQL Server CLR Assembly or Project, you may run into some ugly issues when you now try to push up your new assembly – and it has references to external resources.

The best way to deal with this in my experience is just to clean house (i.e., destroy your CLR assembly) and then re-register it.

To do this:

1) DROP any sprocs, udfs, triggers, types, etc that DEPEND upon YOUR CLR Assembly.

2) Issue a DROP ASSEMBLY MyAssemblyNameHere command – and you’ll now free up all the ‘references’ that this assembly thinks it has.

3) (With your external assembly already registered/loaded) Issue a CREATE ASSEMBLY MyAssemblyNameHere command – or push ‘Deploy’ from Visual Studio again – and it will push your CLR Assembly up to the server.

4) Recreate any of your sprocs, udfs, triggers, etc that you nuked in step 1.

In my recent presentation on Common SQL Server Security Mistakes, a question was asked about what to do with the public role – from a security standpoint.

My response was that, in the majority of cases, the public role isn’t something that you’ll need to worry about. But, that since the public role does ‘leak’ or ‘disclose’ information about objects and databases on a given server, that can be a problem in a very small minority of environments. I therefore recommended, that in those (typically extreme) edge-cases, that removing the public role would make sense – only that if you do undertake such an endeavor, you should watch out for potential side effects.

In this post I’m going to clarify that answer – and provide some examples.

Clarification

As a point of clarification, you can’t remove public at all. You can’t remove the public role from your server, nor can you remove it from your databases. Likewise, you can’t remove logins or users from membership in the public role. (Every once in a while you’ll see questions on forums from someone who has MANAGED to do this – but it effectively breaks SQL Server and isn’t supported. Not sure how people manage to do this either – and don’t care, because i’m NOT advocating it.)

Being a member of the public role is a requirement for working with SQL Server.

So, when I mentioned that you might want to REMOVE the public role in some cases, what I actually SHOULD have said was that: in some cases, you might want to remove the VISIBILITY that the public role grants; but, in doing so, just be forewarned that in some cases that MIGHT cause problems with some logins in some very goofy edge cases.

Examples

The best way to get a sense for what I’m talking about is to look at some concrete, step-by-step, examples.

For these examples, let’s assume the following:

• You’ve got a highly sensitive database called Customers.
• You have OTHER critical databases on the same Server.
• You have a web application – that connects to the server’s Customer database via a Login called Web_App[1].
• You also have some logins for employees within your organization – who need to access the server for regular reports or something. We’ll call one of these user Wilson[1].

For these examples, let’s say that you’ve already done due-diligence from a security standpoint – and both the Wilson and the Web_App logins have been mapped as users into the Customers database with minimal permissions – such as being granted db_datareader, db_datawriter and/or the the ability to execute a couple of trusted stored procedures.

Under a scenario like this, if Wilson were to log into SQL Server Management Studio against your server, his membership in the public server role (all logins are made members of the public role at the server level) would grant him a view of all databases, like so:

And, since the Web_App login is also a member of the public server role, if hackers were able to compromise your web app, and gain access to the server (i.e. via SQL Injection), they’d be able to do:

And get the following results:

In other words, they’d be able to see the same things that Wilson (or any other login on your server) would see.

In most cases, this is not an issue. If it is an issue, you’ve got two options for dealing with this.

First, you can remove public’s ability to SEE all databases on the server. To do this, just run the following query:

This operation denies all logins (since all logins are members of the public role) the ability to see databases on the server that they don’t OWN. Which means that users that are mapped as db_owner will still see ‘their’ databases within the Object Explorer or if they query sys.databases. (And since the SysAdmin, or sa, account is automatically granted db_owner in all databases, the SysAdmin account will still see everything.)

But, for logins like Wilson or Web_App which are NOT members of db_owner, what they can see is drastically limited, as per the following screenshot:

As you can see, in this case, Wilson isn’t able to see anything – other than the master and tempdbs. In fact, even though Wilson has permissions in the Customers database, he’s still not able to see the database in the Object Explorer. He is, however, still able to log in, and can execute the same queries against the Customers databases as before. He’s just lost the ability to ‘see’ the VIEW for ANY database.

Likewise, if the Web_App user issues the same query against sys.databases as before, they’re only able to see the following:

In this case, there IS an entry for the Customers database – but that’s just signifying permissions in the database – it doesn’t indicate the ability to peruse the schema of that database. In fact, take a look at the results of the following query in terms of what’s returned (apparently just a bunch of Service Broker objects that apparently didn’t get the memo on ‘lock down’ against public ‘disclosure’):

A Less Aggressive Approach

Denying VIEW DATABASE to public is a pretty heavy-handed approach. It’s an all or nothing proposition.

So, if you try that and it doesn’t work, you can easily ‘undo’ it with the following:

More importantly, you can implement the same safegaurds at a much more granular level – meaning that if you want to limit the information disclosed should hackers manage to compromise your web application (through SQL Injection or something else), you can just use the following:

And this will then let Wilson and all other logins on the system continue as before – but the Web_App login will be restricted in terms of what schema it can see or view.

TIP: If you end up locking down an individual user account (or role) so that it can’t see all databases in the Object Explorer, do your users a favor and make sure that the default database specified in their login is the database that they can use. Otherwise they’ll have to do a USE databaseNameHere query just to get started with things – as they can no longer right-click on the database in question from the Object Explorer and select the New Query option.

Best Practices

Ultimately, hiding databases from members of the public role is something you really don’t need to worry about in most environments. Though there’s no harm in restricting application logins or other publicly-facing accounts in this way. In fact, it’s a best practice to make sure that any applications have their logins RESTRICTED with DENY VIEW ANY DATABASE TO <appLoginNameHere> once the app is up and running. It’s a simple, added, extra precaution that helps contribute to defense-in-depth by mitigating any potential issues with information disclosure.

Of course, if you’re working in an environment where SQL Injection is a possibility (and sadly, there are plenty of apps out there where SQL injection IS a possibility, but the app can’t be immediately taken offline and fixed like it should be), then using this approach can be part of a mitigation strategy. It is not, however, a sufficient protection. (All it can do, when combined with granting applications least-privilege access to ONLY the resources they need, is mitigate the scope of damage hackers can/will inflict once they compromise your application.)

And, in highly sensitive environments where employees need to be denied access to even knowing about the existence of certain databases, using the approaches outlined here can be an easy way to accomplish those goals. I’d just recommend going with the least heavy-handed approach possible (by blocking individual logins instead of the public role) when feasible.

Otherwise, just be aware that in some edge-cases if you DENY VIEW ANY DATABASE to some users, they’ll actually encounter full-blown login problems in some goofy cases (I swear I’ve seen some with SQL Server Reporting Services databases) where SSMS won’t like this restriction. Though, problems with this should be ‘once in a blue moon’ problems – which is further reduced by the fact that implementing the suggestions outlined here really don’t apply to most environments. 

Footnotes:
[1] I’m using SQL Server Authentication in these examples (and with STRONG (i.e. LONG) passwords this is secure enough – but in really highly-sensitive environments, you’re better off using trusted security).