Getting Session ID in SQL Azure programmatically

There is a Session Id assigned to each SQL Azure connection. The Session Id can be very useful in debugging and can be logged by the developers. In this article, I will show how to fetch the Session Id programmatically.

The first step is to create a class with the properties:

1. Server Name
2. Login Name
3. Password

So let us create a class called SessionID with the following properties:


This class will contain a function to fetch the session Id. Essentially the Session Id for a connection is returned using Context_Info(). We will fetch Context_Info() using SqlCommand.


After fetching Context_Info() as a string, I am converting that to a Guid. The full code for SessionID class is as below:

SessionID.CS

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient; 
 
namespace GettingSessionID
{
  public   class SessionID
    {
 
     // public string DomainAddress { get; set; }
      public string ServerName { get; set; }
      public string LoginName { get; set; }
      public string Password { get; set; }
 
      public SqlConnection GetSessionId(out Guid sessionId)
      {
          sessionId = Guid.Empty;
          SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
          builder.DataSource = string.Format("tcp:{0}.database.windows.net,1433", this.ServerName);
          builder.InitialCatalog = "master";
          builder.UserID = this.LoginName;
          builder.Password = this.Password;
          builder.Pooling = true;
          SqlConnection conn = new SqlConnection(builder.ToString());
          conn.Open();
          using (SqlCommand cmd = conn.CreateCommand())
          {
              cmd.CommandText = "SELECT CONVERT(NVARCHAR(36), CONTEXT_INFO())";
              string contextInfo = (string)cmd.ExecuteScalar();
              sessionId = new Guid(contextInfo);
          }
          return conn;
      }
 
    }
}

In the above code, I am creating a connection string using SqlConnectionStringBuilder.

To get the Session Id, all you need to do is just create an instance of the SessionID class and call the function GetSessionId().

I am getting the Session Id from a console program as below. If you want, you can log the session id from here for debugging purposes.

Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace GettingSessionID
{
    class Program
    {
        static void Main(string[] args)
        {
            SessionID session = new SessionID { ServerName = "giveurservername", Password = "giveurpassword", LoginName = "
giveurloginname" };            Guid sessionId;
            session.GetSessionId(out sessionId);
            Console.WriteLine(sessionId.ToString());
            Console.ReadKey(true);
        }
    }
}

The expected output would be: