more quick actions
minimize quick actions
Topic starter
Hi,
Nitesh needed to get Chrono in a date and time format including milliseconds (contrary to TS field)
I advised him to implement KB492 but while doing it, he got the following message ('clr strict security' ... sp_configure):
After searching on the Internet there are some blogs such as:
https://stackoverflow.com/questions/60395574/how-to-disable-clr-strict-security-in-sql-server
So it is possible to go through by changing the lines of the Deploy script.
reconfigure;
-- Turn advanced options back off
--EXEC sp_configure 'show advanced options' , '0';
--go
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;
I Understand it is not safe, not recommended at all but the purpose was to have a ready to use solution at once.
Do you have a proper way to do it or a safer workaround.
Thanks
Regards
Edouard
Posted : 20/01/2022 3:10 pm
Hello Edouard,
While waiting for feedback from others for your main query, just to share, I have found an alternate way of getting timestamps from Chrono, without using the DLL. The solution is, of course, involves simple calculation to get the timestamp and convert it to DateTime format.
CAST((convert(bigint, [Chrono]) / 864000000000.0 - 109207) AS DATETIME) as Datetime
Notice the 864000000000.0 which represents the number of 100 nanoseconds per day has '.0' at the end to ensure we don't get integer division. The 109207 is our calculated number of days between 1601-01-01 and 1900-01-01, including leap days.
Sample query to get a timestamp in UTC
SELECT TOP (1000) [Chrono] ,[Name] ,[Value] ,[Quality] , CAST((convert(bigint, [Chrono]) / 864000000000.0 - 109207) AS DATETIME) as Datetime FROM [TestTrend_DATABASE1].[dbo].[TRENDTABLE1] where Chrono>0 order by chrono desc
Sample query to get a timestamp in local timezone
SELECT TOP (1000) [Chrono] ,[Name] ,[Value] ,[Quality] , CONVERT(datetime, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, CAST((convert(bigint, [Chrono]) / 864000000000.0 - 109207) AS DATETIME)), DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) as Dt FROM [TestTrend_DATABASE1].[dbo].[TRENDTABLE1] where Chrono>0 order by chrono desc
It is a straightforward calculation, but I am not sure how is the performance when it is done for a large amount of data compared to using the DLL. It can also be used to create a view, so the user does not need to remember the query all the time.
References :
Converting Active Directory Timestamps in Microsoft SQL Server
SQL query to convert UTC to local time zone
Posted : 24/01/2022 5:01 pm
Hi,
It is definitely not recommended to deactivate this option as stated in Microsoft documentation:
A CLR assembly created with
PERMISSION_SET = SAFEmay be able to access external system resources, call unmanaged code, and acquire sysadmin privileges
An alternative is to use another stored procedure, sys.sp_add_trusted_assembly, to declare the assembly alongside its SHA-512 hash:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-add-trusted-assembly-transact-sql?view=sql-server-ver15
This method guarenties that the loaded assembly has not been modified by a potential attacker.
Note that I haven't tested this solution, it still needs to be validated.
Posted : 24/01/2022 7:49 pm