Time Source in Azure SQL

Avi Stokar 6

I need to certify my software with a certifying body (with proctor) and demonstrate and/or show documentation for th source of the time used by my database which stamps all records with current UTC time using triggers.

Is there any documentation for the time source for Azure SQL (NOT managed instance)?

Oury Ba-MSFT 16,076 Reputation points Microsoft Employee

2021-09-27T17:38:49.57+00:00

@Avi Stokar Thank you for posting your question on Microsoft Q&A.

Please go through this blog.
Let us know if you have additional query

Regards,
Oury
Oury Ba-MSFT 16,076 Reputation points Microsoft Employee

2021-09-27T20:47:48.63+00:00

@Avi Stokar Please mark as accepted answer if the above reply was helpful.

Regards,
Oury
Avi Stokar 6 Reputation points

2021-09-27T21:32:36.203+00:00

This does not answer my question. I understand how to get the time from the database. My issue is where is the documentation for showing where the database gets the time. On a server based instance it gets it from the OS and this can be show to be in compliance by showing the server runs NNTP. In serverless environment this is not possible.

Microsoft says Azure SQL is PCI-DSS compliant but I need to be able to show a proctor where the server time sync is documented
Oury Ba-MSFT 16,076 Reputation points Microsoft Employee

2021-09-28T21:07:39.907+00:00

Hi @Avi Stokar Thank you for clarifying this. When you said NNTP did you mean NTP (Network Time Protocol)?
If yes, there are are few documentation pages related to Windows Server and SQL Server that talk about the server time.

“Network Time Protocol (NTP) is the default time synchronization protocol used by the Windows Time service in the operating system.”
https://learn.microsoft.com/en-us/windows-server/networking/windows-time-service/how-the-windows-time-service-works#:~:text=Network%20Time%20Protocol%20(NTP)%20is%20the%20default%20time%20synchronization%20protocol%20used%20by%20the%20Windows%20Time%20service%20in%20the%20operating%20system.

https://learn.microsoft.com/en-us/sql/t-sql/functions/getdate-transact-sql?view=sql-server-ver15#:~:text=Returns%20the%20current%20database%20system%20timestamp%20as%20a%20datetime%20value%20without%20the%20database%20time%20zone%20offset.%20This%20value%20is%20derived%20from%20the%20operating%20system%20of%20the%20computer%20on%20which%20the%20instance%20of%20SQL%20Server%20is%20running.

Let us know if that answer your question

Regards,
Oury

2 answers

Ronen Ariely 15,096 Reputation points

2021-09-28T23:30:53.61+00:00

Good day,

Is there any documentation for the time source for Azure SQL (NOT managed instance)?

Yes there is a clear documentation. For the GETDATE() fore example here is the documentation:

https://learn.microsoft.com/en-us/sql/t-sql/functions/getdate-transact-sql?view=sql-server-ver15

It is explicitly inform us that: "Azure SQL Database (with the exception of Azure SQL Managed Instance) and Azure Synapse Analytics follow UTC."

Syncing of the time is done by the operating system of the hosting machine.

Other functions like SYSUTCDATETIME always returns the UTC rime (on premises or azure database)

Microsoft says Azure SQL is PCI-DSS compliant but I need to be able to show a proctor where the server time sync is documented

I helped several companies to gain their PCI-DSS certification and never asked about this. Moreover, this can be different from one machine to other when using SQL Server on-[remises and yet it does not limit to get PCI-DSS certification obviously. I see no relation for this. You might ask for a different certification or maybe this is your company internal request.

More relevant and important is to make sure you configure the server to use version 1.2 of TLS!

Using the portal go the SQL server -> select Firewalls and virtual networks -> Select the Minimum TLS Version to be 1.2 for all SQL Databases associated with the server,.

In addition, make sure that you disable TLS 1.1 and 1.0 on the client as well.
Please sign in to rate this answer.
Avi Stokar 6 Reputation points

2021-09-29T02:01:24.577+00:00

This did not address my question. I need to demonstrate the time source for Azure SQL. For the certification I am doing I need to demostrate where the server gets it time from and show that i is sync'd to a government based time server. For a server based system this is simple, but for a serverless Azure SQL it does not seem possible to demonstrate this. It would be helpful to show documentation of how Microsoft keeps t t time sync'd on PaaS service

Ronen Ariely 15,096 Reputation points

2021-09-29T02:20:43.593+00:00

Hi,

I need to demonstrate the time source for Azure SQL.

Not clear to me what you mean by "demonstrate the time source"

Using GETDATE() do exactly this. It presents the time source (time of the host). I probably do not get your meaning well

I need to demostrate where the server gets it time from

This make no sense. You cannot demonstrate what happen in the host as you do not control the host. Azure SQL Database is a platform as a service (PaaS) database- service. You cannot directly contact the host. You can present the documentation which explain how it behave if you want, which is what I presented above but you cannot execute anything on the host directly.

For a server based system this is simple, but for a serverless Azure SQL

Exactly true :-) This is the point here regarding to "demonstrate" what happens behind the scenes.

This make no sense to behave differently. It is your choice if this service fit your needs...

But again, this request has nothing to do with PCI-DSS. If someone ask you this in order to provide you PCI-DSS certification then simply leave him to a different official PCI-DSS provider who is approve by all the credit card companies and will not ask you for this :-)
Sign in to comment
Dave Patrick 426.1K Reputation points MVP

2021-09-29T02:25:26.857+00:00

In windows one can w32tm /query /source and move up the chain to PDC emulator and finally to the internet source or possibly the hardware clock address. The OP is simply asking for the same audit trail but in the belly of Azure.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment