TSQL Timezone conversion

Working through some extended events I needed to correlate events based to observations from end users. The extended events record the events in UTC so I needed to adjust the time to local time.  Initially I tried doing it in my head and always messed it up, so I just modified my query.

I played around with a couple ways to convert the results to the appropriate local time.  The first way involved calculating the timezone offset and applying it to the datetime value.


SELECT CONVERT(DATETIME, '2020-03-10 13:30') AS UTC,
CONVERT(DATETIME, switchoffset(convert(datetimeoffset, '2020-03-10 13:30'), DATENAME(TZOffset, sysdatetimeoffset()))) as LocalDateTime;

This results are:

UTC                     LocalDateTime
----------------------- -----------------------
2020-03-07 13:30:00.000 2020-03-07 08:30:00.000

(1 row affected)

The LocalDateTime field calculates the timezone offset to be -5 and subtracts that from the UTC date returning the correct value.  The problem is when you run into Daylight Savings Time (DST).  For example, if I run the above function today (3/10/2020) which is currently in DST, I get an offset of -5.  But if I apply want to convert a date prior to DST, for example last Tuesday (3/3/2020) I will still get a value of 8:30.  However, the offset on 3/3/2020 should be -6.

There is new functionality available with SQL Serve 2016 plus that takes DST into consideration and is much simpler to use.  To do this you use the AT TIME ZONE clause (https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15).  You can take a time in UTC and specify which timezone you want it converted to.  To see this in action, look at the following two queries.


SELECT CONVERT(datetime, '2020-03-07 13:30') UTC, CONVERT(DATETIMEOFFSET, '2020-03-07 13:30') AT TIME ZONE 'Central Standard Time' AS LocalDateTime;

SELECT CONVERT(DATETIME, '2020-03-10 13:30') UTC, CONVERT(DATETIMEOFFSET, '2020-03-10 13:30') AT TIME ZONE 'Central Standard Time' AS LocalDateTime;

UTC                     LocalDateTime
----------------------- ----------------------------------
2020-03-07 13:30:00.000 2020-03-07 07:30:00.0000000 -06:00

(1 row affected)

UTC                     LocalDateTime
----------------------- ----------------------------------
2020-03-10 13:30:00.000 2020-03-10 08:30:00.0000000 -05:00

(1 row affected)

The first query is not in DST and converts the value 3/7/2020 13:30 to the correct value 3/7/2020 07:30.  Note the value returned is now is off the datetimeoffset datatype withe an offset of -6

The second query takes into consideration the change in DST and converts the value to 3/7/2020 8:30.  This is using an offset of -5.

Similarly, you can easily convert from a local time to UTC.  To do this you just apply the at time zime twice.  The first converts your datetime value to a datetimeoffset datatype with the correct offset, and the second then converts that value to UTC.

SELECT(CONVERT(DATETIME,'2020-03-10 8:30') AT TIME ZONE 'Central Standard Time') AT TIME ZONE 'UTC';

----------------------------------
2020-03-10 15:49:17.393 +00:00

(1 row affected)

To find your specific time zone you can query the sys.time_zone_info view.  This includes the current_utc_offset and if the timezone is in DST.

SELECT * FROM sys.time_zone_info WHERE name LIKE '%Central%'

                                                                
name                               current_utc_offset is_currently_dst
---------------------------------- ------------------ ----------------
Central America Standard Time      -06:00             0
Central Standard Time              -05:00             1
Central Standard Time (Mexico)     -06:00             0
Canada Central Standard Time       -06:00             0
Central Brazilian Standard Time    -04:00             0
Central Europe Standard Time       +01:00             0
Central European Standard Time     +01:00             0
W. Central Africa Standard Time    +01:00             0
Central Asia Standard Time         +06:00             0
N. Central Asia Standard Time      +07:00             0
Aus Central W. Standard Time       +08:45             0
AUS Central Standard Time          +09:30             0
Central Pacific Standard Time      +11:00             0


(13 rows affected)

You can also use the function CURRENT_TIMEZONE_ID() to retrieve the local servers time zone.

Hope this helps!

Verbose truncation message

Do you get frustrated when you insert or update data and you get the message:

truncation occurred.

Microsoft finally heard us and is changing the message to give us more information.  If you are on SQL Server 2019 and have your compatibility level set to 150 then you already get the new error message.  If you aren't so lucky, but are using SQL Server 2017 CU12 or higher then you can enable traceflag 460 to get a detailed truncation message.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15

You can enable this traceflag by running

DBCC TRACEON(460)

This changes the truncation message for your current session to the form:

String or binary data would be truncated in table '<Table>', column '<column>'. Truncated value: '<value>'.

To disable you simply run

DBCC TRACEOFF(460)

You can verify it is off by running

DBCC TRACESTATUS

Viewing Windows Update Files

Windows no longer writes logs entries for Windows Update to "C:\Windows\WindowsUpdate.log".  If you open the file you will see the following:

So if you run get-windowsupdatelog powershell cmdlet, the system will generate log files in your current working directory.

If you run this on a Windows 2016 server you might get the following:

To fix, search the c:\windows\winSxS folder for SymSrv.dll and copy it to: c:\Program Files\Windows Defender\

Next time you run the cmdlet your files will be generated.

However, this generates a file that is basically useless.  To get usable information you need to download the symbol files, etc...

You can view the following technet article for more information:

https://social.technet.microsoft.com/Forums/en-US/4e81956f-9ed0-4dc2-be75-8fb54d065768/windowsupdatelog-no-format-information-found?forum=win10itprogeneral

Running applications as another user

There are many times that you want to run an application as a different user.  You might want to test out security as a different restricted user, or connect to a service on another domain.  This command should be able to help you out.  I wish I knew about it long ago, it would have saved me so much time.

Probably the simplest way to run an application as another user it to use the shift-right-click functionality in windows.  You do this by pressing the shift key and right-clicking on the application icon.  You then should get hte option to "Run as a different User".  You then are prompted for credentials.  Once authenticated the application will start running as that user.

This works great, but there are some issues.  The main issue that I always ran into was that the application is now running as the user you select.  You probably are thinking Duh!  But that means shortcuts to local resources like the Desktop or My Documents may not be accessible.  At the very least they will point to the resources for the account you are running as. 


So what other options are there?  There is a nifty command called runas.exe  There are a number of switches, but the ones I want to mention are /user and /netonly.  The /user switch defines which account you want to use.  You can enter it in the form of user@domain or domain\user. 

The second switch is /netonly. This help says this switch will only use the specified credentials for remote access only.  So when accessing services on the workstation you are logged into, or access local resources like your My Documents, you will be able to access them as you would with out the runas command.  However, if you try to connect to a remote resource on another machine the specified user account is now used.  This is amazing, I wish I would have known of this switch years ago!  This will be such a time saver as I use SQL Server Management Studio and frequently open and save scripts to local drives.

Now how do we use this?  The obvious way is to open a command prompt and type out the command in the following format:

runas.exe /netonly /user:Domain\user application

But that isn't always the best way.  A simpler way might be to create a shortcut.  This way you can simply just click the shortcut to start the application as the other user.  This is ideal if you perform this action a lot, saving you time by not having to type in the command.

To create the shortcut, just right right-click on your desktop and select new shortcut.  In the text box for the location enter the command above.  Next enter the name of the shortcut that you would like.  I like to use something in the form of 'SSMS as domain\user'.  Click finish and you are done.  Now if you want to be fancy then right-click on your new shortcut and select properties.  You can now modify any remaining properties like the Icon.  If you click "Change Icon..." and point to your application, it will probably have an embedded icon that you can use.  You can also right-click and pin to the start menu or taskbar, whichever you prefer.

Now that you have a shortcut you can run the application.  A command window will open prompting you for your password.  Enter it in and press Enter.

 If successful, you will see a message that it is attempting to start the application. 

Once the application starts you will be able to access the remote resources using the specified user.  Hope this helps someone out!

Windows file server quota reporting incorrect usage

Occasionally, I have a user report that a network drive they are working on is reporting that it is full and will no longer let them save documents.  When looking at the drive they calculate that the space used is much lower than what the network drive is reporting.  I'm not sure what causes this, but seems to happen more often when large amounts of data are deleted and Windows has a quota on the drive.

In order to fix, you can initiate a quota scan using the following command on the server:

dirquota quota scan /path:c:\path

Note the path c:\path should be replaced with the actual path to the folder on the server, not the UNC path.  This command will return immediately, but it will start a background task tell windows to rescan the quotas defined on the path provided.  Once complete it will update the quota with the correct usage.  The time it takes will vary depending on the size of the drive and the number of files.

It appears that in Server 2019 this is now deprecated and you need to use the commands in the FileServerResourceManager Powershell module.  To initiate a scan you need to start using the following command:

update-fsrmquota -path "C:\path"

To get quota information you can use get-fsrmquota.  example:

get-fsrmquota -path "C:\path"

Cant open SQL Server Configuration manager

I have run into this error a couple times now.  I'm not sure what causes it.  When trying to open SQL Server Configuration manager you get the following message:

There is a support article that covers how to fix this.

https://support.microsoft.com/en-us/help/956013/error-message-when-you-open-sql-server-configuration-manager-in-sql-se

This article only covers 2005 to 2012, but I can confirm it works with 2017.  You just need to use the correct path.

Basically you run the following command.

mofcomp .\sqlmgmproviderxpsp2up.mof

At this point you should be able to open SQL Server Configuration Manger. Yay!

Installing a certificate for SQL Server

I am going to assume that you have a certificate already loaded into the certificate store.  If not that will be a blog post for another day.  If you are running SQL Server as a local system or the service account is in the administrators group, you do not need to worry about granting permissions to the keys.

1. Start by opening mmc.exe and add the certificate plugin for the local system.  
2. Once loaded, find the certificate that you want SQL Server to use.  Right-click on the certificate and select "All Tasks-> Manage Private Keys..." Now you want to give the SQL Server service account read access to the keys. 
3. If your hostname does not match the common name of the certificate you will need to perform an extra step later on. While you have the certificate screen open in mmc, right-click on the certificate and copy down the certificate hash.  You may need it later on.  
4. Next add the sql server configuration manager snapin to the mmc console.  Expand SQL Server Network Configuration.  Right click on protocols for MSSQLServer.  Click on the certificate tab and select the certificate you just loaded. 
   If you do not see your certificate you will need to assign it manually by modifying a registry key. To set the certificate you need to modify the following key:  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer\SuperSocketNetLib.  You will set the Certificate value to the certificate hash that you recorded in step 3 above.
8. Press OK.  Now click on SQL Server Services and restart the Sql Server service.  If everything works out the service should start up.  If it fails to start check the logs here: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log
9. Open up SQL Server Configuration Manager and expand SQL Server Network Configuration and right click on protocols for MSSQLServer.  Change the box from Force Encryption No to Yes.
10. Verify that connections are being made using ssl by running the following query:
    If the column encrypt_option is true, then the session is encrypted
11. If you don't want to force all users to use ssl, then the client can add the following to their connection string:  Encrypt=True;TrustServerCertificate=True;

Searching for a SPN

There are various reasons that you may need to search for an alias.  Maybe you want to find which service account an SPN is added to, or if any SPNs have been created. You can do this by using the setspn command and adding the -Q switch .  For example, if you want to find all SPNs with the string "davey" in them you would type:

setspn -Q */*davey*

Note that the format of the string needs to be in the format of a SPN.  In other words a forward slash is required.  The text before the slash is the Service Class and the text after is the host, port and service name. You can then use the "*" character as a wildcard in the rest of the string. 

As an example, if you want to search for all SQL Server SPNs with "davey" in them you could use the following:

setspn -Q MSSQLSvc/*davey*

If you are adding a new SPN remember to use the -S switch as that will search and verify the SPN doesn't already exist.