How To: Getting events from SQL Server


Sometimes we need to execute some long-running SQL Server processes (Stored procedures or functions) and it’d be nice to get progress messages, and why not, a percent value in order to show a progress bar in our application.

In the past I’ve used different techniques to archive this, from SQL Assemblies for calling a SignalR server, to execute xp_cmdshell, and so many other esoteric ways.

None of that solved the problem in an elegant way, but well, it worked… more or less…

Changing the point of view

Today I noticed the SqlConnection class has an InfoMessage event, and it can be used for “Clients that want to process warnings or informational messages sent by the server” which is EXACTLY what I was looking for.

It looks promising but, how can I trigger an error from my SQL?

The answer is really E-A-S-Y :)

If you are a TSQL programmer you surely know RAISERROR, which generates an error message in a similar way the throw C# keyword does.

The trick here is its severity argument: It indicates the type of problem encountered by SQL Server, and values under 10 indicate that these errors are not severe. Just warnings or information that SQL server sends to anyone is listening.

And who’s listening? You can imagine…

Putting it all together (show me the code)

Let’s assume we’ve this long-running stored procedure in our database:

CREATE PROCEDURE [dbo].[TestLongRunningSP]
RAISERROR('1) Starting', 0, 0) WITH NOWAIT;
WAITFOR DELAY '00:00:03'; --Wait for 3 seconds

RAISERROR('2) Fetching data', 0, 0) WITH NOWAIT;
WAITFOR DELAY '00:00:05'; --Wait for 5 seconds

RAISERROR('3) Some other actions', 0, 0) WITH NOWAIT;
WAITFOR DELAY '00:00:03'; --Wait for 3 seconds

RAISERROR('4) Calculating', 0, 0) WITH NOWAIT;
WAITFOR DELAY '00:00:02'; --Wait for 2 seconds

RAISERROR('5) Finishing', 0, 0) WITH NOWAIT;

Now let’s create some C# code:

First: A class to send the received messages from our repository to its consumer, which in this case the UI will be just a Windows Form (good old boys never die).

public class SqlMessageEventArgs {
public string Message { get; set; }
public int Progress { get; set; }

Notice the Progress property. We will use it in the last part, be patient ;)

Second: A repository class that open a connection, execute the stored procedure and send messages using a ProcessMessage event. We must explicitly enable the connection to receive these errors and raise handling the InfoMessage event:

public class SqlServerRepo
public event EventHandler ProcessMessage;
public string ConnectionString { get; internal set; }
public SynchronizationContext SyncContext { get; internal set; }
public async Task GetDataAsync() {
using (var con = new SqlConnection(ConnectionString))
con.FireInfoMessageEventOnUserErrors = true;
con.InfoMessage += (s, e) =>
SyncContext.Post(_ => ProcessMessage?.Invoke(
s, new SqlMessageEventArgs
Message = e.Message,
}), null);
var sql = "EXEC dbo.TestLongRunningSP";
var commandDefinition = new CommandDefinition(sql);
await con.ExecuteAsync(commandDefinition);

Note: When using async pattern the received messages come from a different thread and cannot be used to update the UI directly. For this reason I always recommend send to the Repo the current synchronization context and use this Context to post to the main thread.


BOOM: Typical cross thread exception when updating UI from a different thread.

Third: Finally let’s create a simple Windows Form with just a button, a progress bar and a list box).

const string cs = "Data Source=YOUR_SERVER;Initial Catalog=YOUR_DATABASE;Integrated Security=True;"
private async void button1_Click(object sender, EventArgs e) {
var repo = new SqlServerRepo() {
ConnectionString = cs,
SyncContext = SynchronizationContext.Current
repo.ProcessMessage += (ms, me) => {
listBoxLog.Items.Add($"{DateTime.Now} - {me.Message}");
await repo.GetDataAsync();
listBoxLog.Items.Add($"Finished Process!");

This code creates the repo, pass the connection string and the current synchronization context, handles the ProcessMessage event and invoke the GetDataAsync method.

Simply and elegant. No dependent assemblies or external resources.

One more thing

What about the progress bar? Also easy. Just use the state argument of the RAISERROR method on the SP like this:

RAISERROR('1) Starting', 0, 10) WITH NOWAIT;
WAITFOR DELAY '00:00:03'; --Wait for 3 seconds

RAISERROR('2) Fetching data', 0, 30) WITH NOWAIT;
WAITFOR DELAY '00:00:05'; --Wait for 5 seconds

RAISERROR('3) Some other actions', 0, 60) WITH NOWAIT;
WAITFOR DELAY '00:00:03'; --Wait for 3 seconds

RAISERROR('4) Calculating', 0, 70) WITH NOWAIT;
WAITFOR DELAY '00:00:02'; --Wait for 2 seconds

RAISERROR('5) Finishing', 0, 100) WITH NOWAIT;

Then update the GetDataAsync method:

public async Task GetDataAsync() {
using (var con = new SqlConnection(ConnectionString))
con.FireInfoMessageEventOnUserErrors = true;
con.InfoMessage += (s, e) =>
SyncContext.Post(_ => ProcessMessage?.Invoke(
s, new SqlMessageEventArgs
Message = e.Message,
Progress = e.Errors[0]?.State ?? 0
}), null);
var sql = "EXEC dbo.TestLongRunningSP";
var commandDefinition = new CommandDefinition(sql);
await con.QueryAsync(commandDefinition);

And finally the code in the form:

repo.ProcessMessage += (ms, me) => {
listBoxLog.Items.Add($"{DateTime.Now} - {me.Message}");
progressBar.Value = me.Progress;

And that’s all!

Hope you enjoy it, and please, send me your thoughts about this solution ;)

How To: Connect via SFTP using SSH.NET


In these times of APIs-everywhere, it may sound like an anachronism the use SFTP to connect to a remote server and get a list of files for exchanging information, but in the financial world (sadly) it’s more common than you think.

In my current project I’ve to connect to a remote server via Secure File Transfer Protocol (aka SFTP) using a user name, a RSA private key and a phassphrase. Once connected, the goal is to read some files from a remote folder and download them to a local folder.

To accomplish this I recommend you to use SSH.NET, one of the most popular SSH libraries for .NET, available on nuget.


More info about SSH.NET here:

Using SSH.NET to connect to the server

The most difficult part here is configure the connection, as usual. We need to provide the server url and port, in combination with the username and a file that contains the private key of the RSA certificate, and -of course- the passphrase.

Here’s the final code, and it works :)

private static void SFTP_Connect_And_Download_Sample()
var host = "";
var port = 999;
var username = "your_username";
var passphrase = "your_passphrase";
var privateKeyLocalFilePath = @"your_localpath\…\PrivateKeyOpenSSH.ppk";
var remoteFolderPath = "/";
var localFolferPath = @"D:\locafiles\";
var key = File.ReadAllText(privateKeyLocalFilePath);
var buf = new MemoryStream(Encoding.UTF8.GetBytes(key));
var privateKeyFile = new PrivateKeyFile(buf, passphrase);
var connectionInfo = new ConnectionInfo(host, port, username,
new PrivateKeyAuthenticationMethod(username, privateKeyFile));
using (var client = new SftpClient(connectionInfo))
var files = client.ListDirectory(remoteFolderPath);
foreach (var file in files)
using (var targetFile = new FileStream(Path.Combine(localFolferPath, file.Name), FileMode.Create))
client.DownloadFile(file.FullName, targetFile);

Bonus * the trick *

I’ve spent a lot of time before it works, receiving a SSH exception “Invalid private key file”, but the same key file works fine when using the app Filezilla to connect, so… #WhatTheHell :S

PuTTY-User-Key-File-2: ssh-rsa
Encryption: aes256-cbc
Comment: rsa-key-KBL-20171006
Public-Lines: 6
Private-Lines: 14
Private-MAC: d288fffe72914eb62ed60a7e50fd8ce775

The key file (*.ppk) looks something similar to this

This is because the private key must be compatible with SshNet, so we have to convert the private key using PuTTY key Generator (the same app we used to create the certificate). Once opened in PuTTY, just export your file key to OpenSSH and use this new ppk file instead of the previous one.


If you open the new file you will see that the key file now begins with


And ends with


Finally, this is a valid SSH private key and it works like a charm ;)

Hope you enjoy it!

HowTo: Redeploy CLR assemblies to SQL Server (Newtonsoft.Json version issue)



The Story

Hi again,

Some time ago I published a post about how to send notifications from SQL Server using SignalR. We are using SignalR self-hosted in a Windows Service in a separated server, and it had been running fine until now.

This week I had to set up a new SQL server which will replace the old one, which will replace the current ‘preprod’ one, and (entering recursive mode…).

After configuring the new SQL Server settings, I tried to update the previously imported assemblies to the database to the latest versions I’m currently using in the .NET project (Newtonsoft.Json & Microsoft.AspNet.SignalR.Client).

References map:

  • MyAssembly
    • Microsoft.AspNet.SignalR.Client
      • Newtonsoft.Json

As you can see, the app references the SignalR client, which references Newtonsoft dll.

So, in order to redeploy the assemblies it’s necessary to:

  • Remove all the CLR Stored Procedures that are using these assemblies, otherwise you cannot delete the assemblies.
  • Remove the main assembly and then all its dependencies in the correct order (Note: It’s not necessary to remove Framework assemblies if it’s not changed).
  • At this point you’ll be able to create the assembly again via CREATE ASSEMBLY

All dependent assemblies will be created again in the database (in this case Newtonsoft.Json & Microsoft.AspNet.SignalR.Client). Easy…


However, when tried to import the assembly to SQL Server I’ve got this error:

“Assembly ‘Microsoft.AspNet.SignalR.Client’ references assembly ‘newtonsoft.json, version=, culture=neutral, publickeytoken=XXXXXXXXX.’, which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: version, culture or public key mismatch). Please load the referenced assembly into the current database and retry your request.”

I decided to import the assemblies one by one, from down to top.

Firstly I imported Newtonsoft.Json with no problems, but when I tried to import the SignalR client I received this error:


It seems a versions issue, because I’ve imported Newtonsoft.Json version 9, not 6! Let’s see what’s in the assemblies view:

select * from sys.assemblies


It’s correct. I’ve imported version 9 of the library… so why the hell SQL is looking for version 6? Well, if you inspect Microsoft.AspNet.SignalR.Client you’ll see that this library has a reference to Newtonsoft.Json version :S


This version number is used to determine the minimum version number (the library won’t work with previous versions), but it seems SQL Server is trying to match this version number with the assembly version number in its catalog, and if the number is different is unable to import the assembly.

If I’m wrong, please correct me! Thx :)


After spending some hours trying different solutions , the only way to solve that -at this time- is register a previous version of Newtonsoft.Json instead of the latest one.

Any version? Nope.

The only version it worked for me is 6.0.8, previous ‘6’ versions didn’t work because of this error:

“‘Method not found: ‘Newtonsoft.Json.Linq.JValue Newtonsoft.Json.Linq.JValue.CreateNull()’.'”

Finally, once I’ve copied the 6.0.8 version of the Newtonsoft.Json to the server path the import process has worked like a charm.


Now I’m looking for a better solution, because I don’t like using old versions of assemblies. I will update this post with a better solution as soon as possible :D

In the meanwhile, hope this helps.

HowTo: Send Notifications from SQL Server using SignalR



In our current project we’ve to push some notifications directly from the database server (SQL Server 2014) to different apps. For example, we’ve scheduled some SSIS (SQL Server Integration Services) projects that pre-calculates large amounts of data. And well, should be nice notify all the apps when new data is available without unnecessary round trips to database. And you know? we’re already using SignalR in our apps… so, why not try to build a lightweight client for sending SignalR notifications?

Dear architects, please, don’t judge me :P

The idea

  1. I finally decided to create a small project in C# that connects to our SignalR (which is self-hosted in a Windows Service). That project contains a single method for sending notifications with a message (just a string for simplify).
  2. Once the project is ready and tested, the next step is create an ASSEMBLY, that is, a module that contains class metadata and managed code as an object in an instance of SQL Server. By referencing this module, common language runtime (CLR) functions, stored procedures, triggers, user-defined aggregates, and user-defined types can be created in the database.
  3. And finally, create a Stored Procedure that use our previous assembly CLR function (for isolation purposes and decoupling), and then use it in our SSIS projects with a simply ‘Execute SQL Task‘ when the process finalizes.

Creating the C# project

Create a blank class Library project and add the SignalR client (Nuget will add depending assemblies -like Newtonsoft.Json- automatically)


Once we added SignalR Client references to the project we need to create the C# method, so let’s add a class item to the project with this code:

public class SqlServerNotificationsClientService
    internal static HubConnection hubConnection = null;
    internal static IHubProxy hubProxy = null;
    public static void SendMessageToHub(
        string severURL, string hubName, string hubMethod, params object[] args)
            if (hubConnection == null || isConnectionClosed())
                hubConnection = new HubConnection(severURL.Trim()) 
                { Credentials = CredentialCache.DefaultNetworkCredentials };
            if (hubProxy == null || isConnectionClosed())
                hubProxy = hubConnection.CreateHubProxy(hubName.Trim());
            if (isConnectionClosed())
            if(args.Length == 0)
                hubProxy.Invoke(hubMethod.Trim(), args).Wait();
        catch (Exception exc)
            SqlContext.Pipe.Send(string.Format("{0}.{1} error: '{2}'{3}", 
                hubName.Trim(), hubMethod.Trim(), exc.Message, Environment.NewLine));

    private static bool isConnectionClosed()
        return !(hubConnection.State == ConnectionState.Connected
            || hubConnection.State == ConnectionState.Reconnecting
            || hubConnection.State == ConnectionState.Connecting);

Note: Before continue, take a look to the last parameter ‘args’. This parameter allows you to pass a different number of arguments to this method, which is nice because when we invoke the hub’s method we can use the same argument type. Unfortunately this argument type is not compatible with SQL CRL and will crash when creating the Stored Procedure in the database :( So we need to change this argument to a simple string.

This code is just an example, maybe would be a better idea split it into different methods (remember the ‘S’ of SOLID?) :)

Let’s assume the code is ok and is able to connect and send the notification. Now…

Create the SQL Assembly

In order to deploy your assemblies to a SQL Server you need the same version of the .NET Framework installed on the server. Yep, it’s too obvious but…

Create a folder in the server called ‘C:\SQLServerAssemblies’ (or wherever you want) and copy all the assemblies generated by the previous project.


But we’ve not finished yet… Now we’ve to add some related assemblies (assemblies dependencies). Otherwise, assembly creation will fail.


You can find these assemblies in the same server, in the correct version of .NET Framework. In my case:

C:\Windows\Microsoft.NET\Framework64\v4.0.30319 -> Microsoft Framework 4.5

Once we’ve all the necessary assemblies copied, let’s create the assembly. First we need to activate some things in the server:

ALTER DATABASE Falcon SET trustworthy ON

exec sp_configure 'show advanced options', 1;

exec sp_configure 'clr enabled', 1;

exec sp_configure 'show advanced options', 0;

CREATE ASSEMBLY Falcon_SqlProxy from 


Yes! Assemblies created successfully in SQL Server ;)

Creating the SP

Finally we only need to create the Stored Procedure. I recommend you use a custom schema. I’ve created one called ‘clr’

@url nchar(125) ,
@hubName nchar(75),
@hubMethod NCHAR(75),
@message NVARCHAR(MAX)
EXTERNAL NAME [Falcon_SqlProxy].[Falcon.NotificationsClient.SqlProxy.SqlServerNotificationsClientService].SendMessageToHub

Remember the note? Have you changed the last argument of the method  SendMessageToHub to a string?

If you did it and copied all the assemblies to the folder it should have worked like a charm ;)

Now it’s time to test our SP… Success!



If you wanna use this SP from a SSIS project to send notifications to your apps, just add a Execute SQL Task to your project and configure the SQL statement:


Hope this helps! :)

Curiosity: What happens if you remove all the Trim() in the c# code? Why connection to SignalR fails? ;)

See you,

DotNet Spain Conference 2015

Este fin de semana he tenido la oportunidad de participar en el mayor evento de la comunidad .NET en España hasta la fecha.

Se ha realizado en la Universidad de Alcalá, con record de asistentes. Cosa que no me extraña viendo la cantidad de charlas y hands on lab que se han realizado en varios tracks en paralelo. La verdad es que ha sido un éxito a todos los niveles, y sólo puedo decir que va a ser difícil superarlo en futuras ediciones. Se ha puesto el listón muy alto! :D


En lo que respecta a mi, tuve la gran suerte de presentar -y que me aprobaran- una charla conjunta con mi colega Alex Casquete, sobre buenas prácticas sobre async, algo que precisamente no es mainstream pero que curiosamente ha tenido una aceptación muy buena. De hecho ha sido mejor de la que yo pensaba que podía tener en un principio, así que muchas gracias a todos por tanto feedback positivo!

Os dejo aquí las slides de la charla y las demos que utilizamos, ya que -para mi sorpresa- me las ha pedido bastante gente:

Sin embargo, en un evento como este no todo son sesiones técnicas. Lo mejor de todo es que he tenido la oportunidad de volver a encontrarme con un montón de viejos conocidos, con los que compartir buenos momentos y algunas cervezas, de modo que el networking ha sido… fantástico!

Os dejo con algunas fotos del eventazo. Gracias a todos! :D

Today’s review – Advanced Installer

Hi again guys! :)

Today I’d like to tell you why I decided to change my software deployment method.


I’ve spent a lot of time in the last days looking for an alternative way to deploy client applications. During this period I’ve tested different options (including the options provided by Microsoft Visual Studio) and finally I’ve decided to use Advanced Installer.

But before that I wanna tell you why I’ve arrived to this point.

My situation

As a developer I’m still building some Windows applications, because most of these applications are targeted to the business market. And well, until now I’ve been using the typical Setup projects or ClickOnce, depending on each project.

Until now.

Because ClickOnce is not the best solution for complex deployments. It’s quite good for simple or medium scenarios, but is not enough when you need to use merge modules -or equivalent-, database scripts, registry entries, launch conditions, etc.

What about Setup projects? Well, I decided not to use them anymore since I read this post. Because these modules won’t be supported in future versions of Visual Studio. If you haven’t read this post, and you’re still using it, you must read it now.

So, in the previous link you’ve noticed that WiX is recommended instead of Setup projects, but in my opinion WiX is not a valid option, because doesn’t allow neither use designers to customize the installer nor install prerequisites.

For your information: The following table compares the features of Windows Installer Setup Projects, InstallShield 2010 Limited Edition, and the Windows Installer XML Toolset.

Advanced Installer

This application provides a powerful and easy way for making your deployments via Windows Installer plattform, offering different editions: From a freeware with the most common options to an edition for architects, with lots of stunning features like integration with App-V, VMware or System Center deployments.

In my particular case I downloaded a trial version (limited to 30 days) and started to make a test with a medium-size solution, with 20 projects inside. After playing a couple of hours with the tool, I built a complete setup project, including some advanced features like: Merge modules, file associations, launch conditionals (both system and software), prerequisites, registry entries and sql scripts. And of course with complete control over the installation options and the appearance of the setup wizard.


WOW! Not bad at all… And most of these options are provided by free (not all but most of them).

Coming soon

Now I’m thinking in implement some advanced features, like licensing, trials  and updates. Yes, you can implement a licensing mechanism, and not only in a simply way. You can use your own C++ DLL, or send a POST to a URL to validate the license. It sounds really good!

Another interesting feature when using serial validation allows you to make trial packages. It allows your users to evaluate your product before purchasing, and also enables users to register after they have received a valid key. After the trial period expired the user will be able to run your application only after registration.


Finally, my favorite: Advanced Installer provides a way to check for updates. If this option is enabled, before installing the application package, a version check will be executed. If a newer version of the installation package (current MSI) is available it will be downloaded and installed instead of the current one.

My review

A stunning product, with lots of options for making your deployments quickly and easy. You can customize your setup experience in great detail and the UI helps you to create complex installation packages in minutes.

Compared with other market leaders is much easier to use and it has a shortest learning curve.

Sincerly impressed. Good job guys! :)

Advanced Installer website:

PS – <IRONIC MODE ON> Ok. I understand traditional Windows applications are from the past, and nowadays everybody is developing web applications or WP7/Metro applications… this kind of developments doesn’t need too complex deployments, and maybe it’s better for everybody the marketplace-based model… But there are a few people that are still developing business applications. I just wanna say… PLEASE MICROSOFT, DON’T FORGET US! :)

Using ASP.NET Membership in SQL Azure

The scenario:

Sometimes we forget SQL Azure is not SQL Server. This morning in the project I’m working, we needed to create a small ASP.NET membership database in SQL Azure.

It seems easy! Would you expect troubles with this? Nope? Ok, me neither… until I tried to execute aspnet_regsql to create the database:

An error occurred during the execution of the SQL file ‘InstallCommon.sql’. The SQL error number is 208 and the SqlException message is: Invalid object name ‘master.dbo.sysdatabases’.

Notice that this error is absolutely normal, because in SQL Azure we can’t access to sysdatabases table. In fact, if we try to execute this script:

SELECT * FROM master.dbo.sysdatabases

We will receive this error:

Reference to database and/or server name in ‘master.dbo.sysdatabases’ is not supported in this version of SQL Server.

The solution:

When using SQL Azure we cannot create the database with aspnet_regsql. Instead, we should use a new set of scripts optimized for SQL Azure.

There are a couple of files to download, but we only need which contains all the needed scripts separated by themes (memberships, roles, etc.) which I think is great! So we will install only the parts that we will use:


The most important step is installing the scripts in the correct order. The first one must be InstallCommon.sql, and after (in my particular case) installing the InstallMemberShip.sql and InstallRoles.sql scripts is -by far- enough.

Note: Be aware with the scripts, I’ve had to execute the membership script twice, because the first one some tables haven’t been created. If you have the same problem please send me feedback about it.

Hope you enjoy it! :-)

More info:

ASP.NET Membership scripts for SQL Azure:

General Guidelines and Limitations (SQL Azure Database)

How to: Grouping groups


Today I would like to show a tip for grouping active directory groups using LINQ to objects.

Suppose this scenario: You wanna retrieve all the roles a user belongs, grouping them by their domain name, as shows:

Groups under:
– Group name: All
– Group name: LOCAL
Groups under: BUILTIN
– Group name: Users
– Group name: Administrators
– Group name: xxx1
– Group name: xxx2
– Group name: xxx3
– Group name: xxx4
– Group name: xxxN
Groups under: NT AUTHORITY
– Group name: INTERACTIVE
– Group name: Authentified users

(*) As you can imagine, some names have been deleted for security reasons :-)

We can accomplish this using only one LINQ to objects sentence, and a couple of extension methods (extending the NTAccount class):

public static IOrderedEnumerable<IGrouping <string, NTAccount>>
    GetGroupsUnderDomains(this WindowsIdentity identity)
    var groups =
        from grIdentity in identity.Groups
        where grIdentity.IsValidTargetType(typeof(NTAccount))
        select grIdentity.Translate(typeof(NTAccount)) as NTAccount into ntAccounts
        let domainName = ntAccounts.GetDomainName()
        orderby domainName
        group ntAccounts by domainName
            into domainGroups
            orderby domainGroups.Key
            select domainGroups;
    return groups;

public static string GetDomainName(this NTAccount account)
    string[] split = account.Value.Split('\\');
    return split.Length == 1 ? string.Empty : split[0];

public static string GetAccountName(this NTAccount account)
    string[] split = account.Value.Split('\\');
    return split[split.Length - 1];

Awesome! LINQ rules :-)

If you would try this code, it’s quite simple:

var groups = WindowsIdentity.GetCurrent().GetGroupsUnderDomains();
foreach (var dg in groups)
    Console.WriteLine(string.Format("Groups under: {0}", dg.Key));
    foreach (var g in dg)
        Console.WriteLine(string.Format("  - Group name: {0}", g.GetAccountName()));

HYEI, happy coding!

December 2010

How to: Check if current user is member of ‘domain admins’

The scenario

Sometimes in business applications it’s interesting checking if current user is member of the ‘domain administrators’ role. For example I am used to checking if current user has administrative privileges in order to showing some advanced configuration options (changing the application’s connection string, or allow creating new users).



To acomplish this we could use WindowsPrincipal class and its IsInRole method (this method checks if an user is member of a Windows role and returns a bool value). One of its overrides allows to pass the SID of the role or a constant value based on the enumeration WindowsBuiltInrole.

Note: For performance reasons, it’s recommended to use the override: IsinRole(SecurityIdentifier).

To check if current user is a local administrator we only need to do this:

WindowsPrincipal wp = new WindowsPrincipal(WindowsIdentity.GetCurrent());
return wp.IsInRole(WindowsBuiltInRole.Administrator);

Notice that it’s realy easy, but as -its name indicates- WindowsBuiltInrole enumeration only contains local roles. So, if we would check if our user is member of a domain group, we should find the role SID, and then copy this value in our code.

As you can imagine, this is not the best solution, isn’t it? Well, let’s investigate a little bit more…


Let’s take a look at the following enumeration WellKnownSidType, this enumeration provides commonly used security identifiers. Uhm… sounds good! Let’s try to use it in our code:

WindowsPrincipal wp = new WindowsPrincipal(WindowsIdentity.GetCurrent());
SecurityIdentifier sid = new SecurityIdentifier(WellKnownSidType.AccountDomainAdminsSid, null);
return wp.IsInRole(sid);

Do’h!It seems that we need to pass the second argument called DomainSId…



What the hell means DomainSId? At MSDN we can read a brief explanation: Represents the domain SID, and this value is required for some WellKnownSidType values.

At this point our goal should be know the domain SID, but wait… how can I retrieve the domain SID? After spend some time surfing the Internet, the only solution I could find was an utility called PSGetSid from Mark Russinovich inside PSTools components. If you want try it, you can download this utility and execute it from your console to know the SID of your domain (type your domain name as “” o “”):


However, I’m pretty sure exists a better solution to this issue. Thus, let’s try to ask to our domain their SID, using the namespace System.DirectoryServices:

Domain d = Domain.GetDomain(new DirectoryContext(DirectoryContextType.Domain, getDomainName()));
using (DirectoryEntry de = d.GetDirectoryEntry())
    byte[] domSid = (byte[])de.Properties["objectSid"].Value;
    string sdomainSid = sIDtoString(domSid);

Here we need a couple of auxiliar methods. The first one retrieves the domain name, and the second one retrieves a string that represents a SID value (from a byte array).

public static string getDomainName()
    return IPGlobalProperties.GetIPGlobalProperties().DomainName;

public static string sIDtoString(byte[] sidBinary)
    SecurityIdentifier sid = new SecurityIdentifier(sidBinary, 0);
    return sid.ToString();

Then, the value of sdomainSid corresponds to the SID of our domain. Great! Now, we can use it to retrieve the SID of the domain administrators role.

Putting it all together

Like my collegue and friend @alegrebandolero, I’m also a fan of extension methods. So, let’s create an extension method for the WindowsIdentity class:

using System.DirectoryServices;
using System.DirectoryServices.ActiveDirectory;
using System.Net.NetworkInformation;
using System.Security.Principal;

namespace TestAD
    public static class SecurityExtensions
        public static bool IsDomainAdmin(this WindowsIdentity identity)
            Domain d = Domain.GetDomain(new
                DirectoryContext(DirectoryContextType.Domain, getDomainName()));
            using (DirectoryEntry de = d.GetDirectoryEntry())
                byte[] domainSIdArray = (byte[])de.Properties["objectSid"].Value;
                SecurityIdentifier domainSId = new SecurityIdentifier(domainSIdArray, 0);
                SecurityIdentifier domainAdminsSId = new SecurityIdentifier(
                WellKnownSidType.AccountDomainAdminsSid, domainSId);
                WindowsPrincipal wp = new WindowsPrincipal(identity);
                return wp.IsInRole(domainAdminsSId);

        private static string getDomainName()
            return IPGlobalProperties.GetIPGlobalProperties().DomainName;

That’s all. Now, use it as follows:

if (WindowsIdentity.GetCurrent().IsDomainAdmin())
    //Some actions…

Edit 12/14/2010: Since Windows Vista, each Windows user have a couple of security tokens. The first one is the normal token with limited privileges, and the second one only works when you ‘run as administrator’. This code only works if you are using the second token, running the application as administrator.

HYEI, happy coding!

December 2010