My technical musings

Recovering the licence key in a Dynamics 365 on-premise system.

Needing to verify that the correct licence key had been used for a Dynamics 365 on-premise upgrade I realised that the Deployment Manager will allow you to change the key that’s being used, but it won’t allow you to see the current key.

Being on-premise it made life slightly easier as all I had to do was break out the SQL Management studio and run the following query.

select NVarCharColumn from MSCRM_CONFIG.dbo.ConfigSettingsProperties where ColumnName='LicenseKeyV8RTM'

Of course you will need suitable rights to the MSCRM_CONFIG database to be able to run this query.

Using the CLR to create a Random Number generator

Using the CLR to create a Random Number generator


Following on from my last post about a random number generator in SQL, another method of providing a random number generator would be to add the function using C# and the Common Language Runtime, and fortunately the tools to do this are now installed with SQL Server.

We could do this using Visual Studio but for a small function like this we can do it all with notepad and the command line compiler installed with .net.

The first thing to do is check that the compiler is accessible and that the path to the csc.exe file is included in your path statement. Open a CMD window and at the prompt type CSC. If you see a message like Microsoft visual C# compiler version x.x.xxxxx.xxxxx then all is well. If you get an error message then you need to check you path statement.

The compiler will be installed in the c:\windows\microsoft.net\framework\(version) folder where version is the version of dot net that you have installed. If you check in the framework folder you may have multiple versions, and if this is the case go for the latest version on the machine.

Next add the path to the folder into the PATH variable. This is usually located in the Environment Variables option under system properties and the exact method of accessing it will vary depending on the version of the operating system you are running.

Once you have updated your path statement go back to your CMD window and try entering the CSC command again, if all is well you will see the Microsoft visual C# compiler version x.x.xxxxx.xxxxx message, if not check the previous step and retry.

Now that the compiler is working, we can now write the code for the function we wish to create. Open a blank file in Notepad.exe and enter the following:

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Security.Cryptography;

public class RandomNumberFn
	public static SqlByte CryptoRnd()
		SqlByte value= new SqlByte(255);
		RNGCryptoServiceProvider rngCsp = new RNGCryptoServiceProvider();
		byte[] randomNumber = new byte[1] ;	
			value = randomNumber[0];
		return value;


If you are familiar with C# this code should be self-explanatory, and uses the RNGCryptoServiceProvider() which is the C# implementation of the CRYPT_GEN_RANDOM() number generator we looked at in TSQL in the last entry.

Save the file somewhere simple as rnd.cs, in my case K:\ as because I was using the command line version of the compiler I wanted something easy to type in.

In the CMD window enter the command csc /target:library K:\rnd.cs

After a second or two it should return you to the command prompt with no errors.

The /target:library switch on the compiler generates a .dll file rather than a .exe. The .dll file will be created in the same directory as the source file and appropriate permissions will be required to ensure the file can be created.

Next we need to add the function into SQL server, and we can do this by opening up the SQL Management Studio and creating a new query window.

In the query window enter and execute

CREATE ASSEMBLY CryptoRnd from 'k:\rnd.dll' with PERMISSION_SET=SAFE


If you have an error about the CLR Integration not being enabled, run the following script:

sp_configure 'show advanced options', 1;
sp_configure 'clr enabled', 1;


This script will only need to be done once, and you will need the ALTER SETTINGS permission for your SQL Server user.


Next enter this query into the query manager and execute it

Create function CLR_RandomNumber() returns tinyint

as external name CryptoRnd.RandomNumberFn.CryptoRnd


This will actually create the function from the CLR code. The External name is in the form <assembly>.<class>.<functionname>


You can now test your new function by executing this query


Select dbo.CLR_RandomNumber()


Or it can be used in place of the random functions previously as


Select *,dbo.CLR_RandomNumber() from mytable.


A SQL Server random number generator

The first function that springs to mind is the RAND() function, but this suffers from a few drawbacks. Firstly it is only a pseudo random number and it generates values between 0 and 1. This is easily rectified by multiplying the result by the range you want, so for values between 0 and 255 the expression would be


select cast(rand() * 255 as tinyint)


A second drawback is if you try to use it in a query to assign every record a unique number it doesn't, it simply generates a single number for all rows.


select top 20 *,cast(rand() * 250 as tinyint) from mytable


This generates the same number for every row.



A more flexible function in SQL server is the CRYPT_GEN_RANDOM() and this will generate a variety of random values and is designed to be cryptographically secure which means it will potentially be more random than the existing RAND() function.


This can easily replace the RAND() function and is called like this depending on the size of the value you wish to return.







Numbers returned by this function will be between the MIN and MAX values for the datatype you are returning and as such may be both positive and negative.


This time if you run the query


select top 20 *,cast(CRYPT_GEN_RANDOM(1) as TINYINT) from mytable


You will notice a different random number returned against each row.


If you only wish to have a value between 0 and 250 it would initially be quite easy to simply use the modulus function to return it, so a query like


select top 20 *,cast(CRYPT_GEN_RANDOM(1) as TINYINT) % 250 from mytable


will provide results in the 0-250 range. A potential issue here is that the values may not be completely random as some of the values between 1 and 5 will have a higher probability of being returned as for these numbers as there are two possible output values that are in the modulus operator that will give the same final result. To output the number 1 the outputs from the random number could be 1 or 251, and for 2 it could be 2 or 252 and so on.


To prevent this being an issue you could simply throw away all the numbers higher than 250 so each number only has a 1 chance of being included in the output.


There are two possible solutions here, firstly we could make the range of the CRPT_GEN_RANDOM() function so large that most of the statistical deviations could be removed and the simplest solution could be




As the output could swing from -9223372036854775808 through 9223372036854775807 the possible values for each of the desired 250 output values are many so that it should produce a nice smooth spread of random numbers. It wont be perfect but for most applications it will be fine.


The second and slightly more convoluted way to remove this deviation would simply be to throw away any numbers higher than 250 and rerun the CRYPT_GEN_RANDOM() function again.


One issue that will be encountered is that the CRYPT_GEN_RANDOM() function cannot be run from within a UDF which would be the simplest way to handle this.


First we have to embed the function in a view as unfortunately embedding it in a stored procedure will not work from within the function.


CREATE VIEW vWGenerateRandomNumber




This view is really more of a workaround as we aren't actually returning any actual database rows just the value from the CRYPT_GEN_RANDOM() function.


Next we can create the function that calls the view

CREATE FUNCTION Get_RandomNumber()
	 /* rerun the loop while the value is out of range */
	WHILE (@Value IS NULL OR @Value >= 250) 
	select @Value= RandomNumber from dbo.vWGenerateRandomNumber


In the function we have added the login in the WHILE loop so that it calls the CRYPT_GEN_RANDOM() function again if the value is > =250 or NULL.


Now whenever we need a random number we can simply call the function.


select top 20 *,dbo.Get_RandomNumber() from mytable

Getting random data out of SQL Server

SQL Server is excellent for storing data and passing it back in nice ordered blocks, but what happens if you just want to take a random selection of data for example a random list of customers to test a new marketing campaign on.

Since SQL2005 there has been a clause called TABLESAMPLE that will allow you to get some random data from a table either as a percentage of the table or a set number of rows.

Select * from mytableTABLESAMPLE(10 PERCENT)


Select * from mytableTABLESAMPLE(100 ROWS)

You can also specify a REPEATABLE option so that if no updates, inserts or deletes have been made to the database inbetween queries the same results will be returned.

Select * from mytableTABLESAMPLE(10 PERCENT) REPEATABLE(1)

The (1) is an arbitrary value, as long as this stays the same between queries and no other changes have been made you will see the same results.

Unfortunately due to the way this clause works under the hood the results may not be truly random as it uses the way the data is stored internally in pages to sample information rather than taking the database rows as a whole and returning the sample from there.

This also has some limitations in that it cannot be used in Views, or Rowsets.


Another more flexible method to get random data out from a sql table is to simply sort by NEWID(), this works by temporarily creating a Guid for each row and sorting by it, and is a very effective way especially when combined with a TOP command to extract a random set of rows. A Guid or Globally Unique Identifier is a computer generated unique reference number that is 128bits long and should be unique as it can be generated with 2^122 unique values. A Guid usually takes the form {21EC2020-3AEA-4069-A2DD-08002B30309D}.

Select TOP 50 * from mytable order by NEWID()

This will return a random 50 rows from the table, but if the query is run a second time it will return a different 50 rows, although it is possible that some of the rows from the first query may appear in the second.

This method has the advantage that the order by clause can be included in almost existing query or view but care has to be taken about the order it appears in if there is already an existing ORDER BY clause.

Select top 50 * from mytable order by name,NEWID()

This will probably not return the results you are looking for as the query will perform a fixed sort before the random part you are looking for.

The query should be rewritten as

Select top 50 * from mytable order by NEWID(),name

This will return a random sample sorted in the order defined after the NEWID().