How to open up a SQL connection with SQL Management studio 2012 through Powershell

Sometimes it is nice to just log into a SQL server database with just running a command. To maintain security, we let our user input the password manually and use Powershell to start up SQL Server Management Studio 2012. If you got another version of SQL Server Management Server (SSMS), just adjust the path to ManagementStudio.

$domain="SOMEDOMAIN"
$user="SomeUser"
$workingdir="c:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio"
$databaseserver="somedbserver.somedomain.net"
$cmd="ssms.exe"
$arguments=" -S $databaseserver" 

$domainuser = $domain + "\" + $user

$response = Read-host "Enter password" -AsSecureString 
#$secpasswd = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($response))

$credential = New-Object System.Management.Automation.PSCredential ($domainuser, $response)

Start-Process -WorkingDirectory $workingdir -FilePath $cmd -Argument $arguments -Credential $credential 

You can add an icon on your desktop and assign it a shortcut to powershell and then paste the script above to a .ps1 file and then as the argument of the shortcut point to a .ps1 with the script above.

How to display the SQL involved for in Entity Framework programatically with DbContext and ObjectContext

As a developer, we often use an Object-Relational Mapper (ORM) to abstract from the way we work with a database. Gone are the days of building a SqLCommand object, setting the CommandText and executing row by row the result set, as used with ADO.NET. Today, most .NET developers use EntityFramework to work with the database. But this abstraction is all and well, and makes us work more efficient. Sadly, many developers are agnostic to the fact that despite they get the results out from the database, they do often do so in a slow manner. The reason of this is often not that there is a lot of data in the database, but we use Entity Framework queries that generate the wrong sql, i.e we get the results, but the SQL involved got a poor performance. You can use LinqPad for example to display the SQL involved in Entity Framework queries. But we can also achieve this using programatically methods, with C#. Here is an extension class I wrote to achieve this. The extension methods works with both data contexts that inherit from DbContext and data contexts that inherit from ObjectContext.

using System;
using System.Data.Entity.Core.Objects;
using System.Linq;
using System.Reflection;

namespace Hemit.OpPlan.Data.EntityFramework
{
    
    public static class IQueryableExtensions
    {

        /// <summary>
        /// Shows the sql the IQueryable query will be generated into and executed on the database
        /// </summary>
        /// <param name="query">The IQueryable to analyze</param>
        /// <param name="decodeParameters">Set to true if this method should try decoding the parameters</param>
        /// <remarks>This is the generated SQL query in use for Entity Framework. This works using ObjectContext</remarks>
        public static string ShowSqlUsingObjectContext(this IQueryable query, bool decodeParameters = false)
        {
            var objectQuery = (ObjectQuery)query; 
           
            string result = ((ObjectQuery)query).ToTraceString();

            if (!decodeParameters)
                return result; 

            foreach (var p in objectQuery.Parameters)
            {
                string valueString = p.Value != null ? p.Value.ToString() : string.Empty;
                if (p.ParameterType == typeof(string) || p.ParameterType == typeof(DateTime))
                    valueString = "'" + valueString + "'";
                result = result.Replace("@" +p.Name, p.Value != null ? valueString : string.Empty); 
            }
            return result; 
        }

        public static string ShowSqlUsingDbContext(this IQueryable query, bool decodeParameters = false)
        {
            var memberInfo = query.GetType().BaseType;
            if (memberInfo != null)
            {
                var internalQueryField = 
                memberInfo.GetFields(BindingFlags.NonPublic
              | BindingFlags.Instance).FirstOrDefault(f => f.Name.Equals("_internalQuery"));
                if (internalQueryField != null)
                {
                    var internalQuery = internalQueryField.GetValue(query);
                    var objectQueryField =
                        internalQuery.GetType().GetProperty("ObjectQuery"); 

                    // Here's your ObjectQuery!
                    if (objectQueryField != null)
                    {
                        var objectQuery = objectQueryField.GetValue(internalQuery) as ObjectQuery;
                        string sql = ShowSqlUsingObjectContext(objectQuery, decodeParameters);
                        return sql;
                    }
                }
            }

            return null;
        }

    }
}

Note that when we use an IQueryable inside a DbContext, the BaseType is actually a DbQuery, and this wraps the ObjectQuery inside a field called "_internalQuery". In addition, we get a property inside this field that is called "ObjectQuery". So we can get hold of the ObjectQuery inside a DbQuery. When we got hold of the ObjectQuery, it is easy to decode the contents using ToTraceString() method and if we want to further decode the parameters EntityFramework generates, we can do so using the Parameters property of ObjectQuery. We can then interpolate the SQL parametrization and get the SQL string most readable, if we like this form. Of course, some developers rather like the parametrized version. There may be some queries that don't have any parameters at all, but this is fine. Of course, all this parametrization business is to hinder SQL injection. Please do not resort to creating methods that accepts such "clean sql", you may easily generate an attack vector into your system if you try do adjusts queries so and not being careful. With this extension method we can easily test it out:

using SomeAcme.EntityFramework;
using Nunit.Framework; 

[Test]
public void TestGettingSomeSql(){

 using (var context = new SomeAcmeContext()){
  IQueryable query = context.SomeDataEntity.Where(x => x.SomeProperty == 123).AsQueryable();
  
  string sql = string.Empty; 

  //If this is a dbContext: 

   sql = query.ShowSqlUsingDbContext(decodeParameters: true);

   //Or if this is an ObjectContext: 

   sql = query.ShowSqlUsingObjectContext(decodeParameters: true); 

   Console.WriteLine(sql);

 }
}

We create a query, using the .AsQueryable() extension method in Linq and we then pass the IQueryable object to the extension methods of the class shown earlier. Of course, the extension method to use depends on the type of data context you work with. Either a DbContext or an ObjectContext.

How to ensure the integrity of information in .NET using Digital Signature Algorithm DSA

This article will concern the topic of digital signature. There are several ways to ensure the integrity of the information or data that is sent. This concerns the concept of non-repudiation, the case that the sender cannot deny that he or she is the true sender of that data. We also can check that the data is correct, so digital signature can act as some sort of checksum - but for the entire message. We are also concerned that the information is authentic and original and not tampered with by an attacker. Digital Signature Algorithm in .NET or DSA uses the SHA-1 Secure Hash Algorithm. There are today more powerful methods to sign data, such as the RSACryptoServiceProvider. But we will in this article use DSACryptoServiceProvider. DSA is today not considered failsafe. There are security vulnerabilities. However, for ordinary use - it is not that easy to break. Just like in RSA, there is a public and private key. The API is very similar to RSACryptoServiceProvider. The following console application shows some central API calls on a DSACryptoServiceProvider.

using System;
using System.Security.Cryptography;
using System.Text;

namespace DSASignDemo
{
    class Program
    {

        // ReSharper disable once UnusedParameter.Local
        static void Main(string[] args)
        {
            var dsa = new DSACryptoServiceProvider(1024);
            var publicDsaParameters = dsa.ExportParameters(false);
            var privateDsaParameters = dsa.ExportParameters(true);
            string inputText = "Burgers and coca cola";
            byte[] inputData = Encoding.Unicode.GetBytes(inputText);
            byte[] signedBytes = SignData(inputData, privateDsaParameters);
            bool isVerified = VerifyData(inputData, signedBytes, publicDsaParameters);

            Console.WriteLine("Input text: " + inputText);
            Console.WriteLine("Signed text: " + Convert.ToBase64String(signedBytes));

            if (isVerified)
                Console.WriteLine("The message was verified");
            else
                Console.WriteLine("The message was not verified");

            byte[] hashData = ComputeHash(inputData);
            Console.WriteLine("SHA-1 computed hash: " + Convert.ToBase64String(hashData));

            bool isHashSame = CompareHash(inputText, Convert.ToBase64String(hashData)); 
            if (isHashSame)
                Console.WriteLine("Hash is the same");
            else 
                Console.WriteLine("Hash is not same");

            byte[] signedHashData = dsa.SignHash(hashData, "SHA1");

            Console.WriteLine("Signed hash: ");
            Console.WriteLine(Convert.ToBase64String(signedHashData));

            bool isVerifiedHash = dsa.VerifyHash(hashData, "SHA1", signedHashData);
            if (isVerifiedHash)
                Console.WriteLine("Hash is verified");
            else
                Console.WriteLine("Hash is not verified");



            Console.WriteLine("Press the any key to continue ..");
            Console.ReadKey();  
        }

        static bool CompareHash(string inputText, string hashText)
        {
            string computedHash = Convert.ToBase64String(ComputeHash(Encoding.Unicode.GetBytes(inputText)));
            StringComparer comparer = StringComparer.OrdinalIgnoreCase;
            return comparer.Compare(computedHash, hashText) == 0; 
        }

        static byte[] ComputeHash(byte[] inputData)
        {
            var shaManaged = new SHA1Managed();
            byte[] hashBytes = shaManaged.ComputeHash(inputData);
            return hashBytes;
        }

        static byte[] SignData(byte[] inputData, DSAParameters dsaParameters)
        {
            try
            {
                var dsa = new DSACryptoServiceProvider();
                dsa.ImportParameters(dsaParameters);
                return dsa.SignData(inputData);
            }
            catch (CryptographicException cge)
            {
                Console.WriteLine(cge.Message);
                return null;
            }
        }

        static bool VerifyData(byte[] inputData, byte[] signedData, DSAParameters dsaParmeters)
        {
            try
            {
                var dsa = new DSACryptoServiceProvider();
                dsa.ImportParameters(dsaParmeters);
                return dsa.VerifyData(inputData, signedData); 
            }
            catch (Exception err)
            {
                Console.WriteLine(err.Message);
                return false;
            }
        }




    }
}

A sample output of running this console application is:

 


Input text: Burgers and coca cola
Signed text: su7Qv+O58MyOzFjWXXx6bq9xAz9GtJ30+N8pmEYA4qFwmCdU04+qWg==
The message was verified
SHA-1 computed hash: b4o//84sCZ5cUY6cfewNia9yHYI=
Hash is the same
Signed hash:
xWExD3udQWayE2nfVDY+w8o/VuuBlKRng5Oe5XZ1zBAJO90BG+dbcA==
Hash is verified
Press the any key to continue ..

Note that the output will differ per run, where it says signed text and SHA-1 computed hash and signed hash. The reason is that the DSA algorithm will choose a random number in part of its steps and the resulting output will give different results. The boolean values here should of course be consistent, i.e. give consistens checks. Some things to note here: - Using the constructor we ask for at least a 1024 bit sized BigNum in the constructor of the DSACryptoServiceProvider to be used to generate the large primes that is involved in the DSA algorithm. - We actually use the private key of DSA to sign data and the public key to verify the data. DSA is an assymetric cryptographic algoritm and the order in which the keys are used is kind of reversed to RSA. It is the sender that sign the data and the receiver that verifies the data with a public key. - For speed, we can sometimes choose to just compute a hash like SHA-1 and then sign this hash. We can then verify hash. This is much quicker than signing large data. So first off, we can compute a SHA-1 hash, then sign the hash and then include this signed hash appended to the message, then let the receiver just verify the signed hash. The receiver will then use the hash and the signed hash and verify the hash and the fact that the message integrity is kept. We must tell the method VerifyHash which algorithm that is used. An overview of the hash algorithm names you can use in one of the arguments of SignHash and VerifyHash methods is available here: https://msdn.microsoft.com/en-us/library/system.security.cryptography.hashalgorithmname(v=vs.110)