Sql-server – How to add Spatial Reference ID 28992 to MSSQL server

geometryspatialsql serversridxml

I want to use Spatial Reference ID 28992, as found on EPSG, in MS SQL Server. How can I add this to MS SQL Server?

My end goal is to get this dataset into MS SQL Server, with the Geography Markup Language (GML) elements are stored in MS SQL Server's geography data type. After this, I want to make a connection to the MS SQL Server from QGIS, so I can do some nice data analyses.

My idea was to parse the GML elements (that are contained in an XML) using the GEOMFROMGML parser from MS SQL Server. I tried:

SELECT geography::GeomFromGml (
'<Polygon xmlns="http://www.opengis.net/gml">
<exterior>
<LinearRing>
<posList>78988.952 384548.144 78992.462 384549.74 78998.712 384555.12</posList>
</LinearRing>
</exterior>
</Polygon>'
, 28992 )

However, the above code gives the following error:
"The spatial reference identifier (SRID) is not valid. The specified SRID must match one of the supported SRIDs displayed in the sys.spatial_reference_systems catalog view."

So, I tried to add the SRID to sys.spatial_reference_systems catalog view, using:

INSERT INTO [DataAnalyse].[sys].[spatial_reference_systems] ([spatial_reference_id]
      ,[authority_name]
      ,[authorized_spatial_reference_id]
      ,[well_known_text]
      ,[unit_of_measure]
      ,[unit_conversion_factor])
VALUES(
    28992,
    'EPSG',
    28992,
    'GEOGCS["Amersfoort", DATUM["Amersfoort", SPHEROID["Bessel 1841", 6377397.155,299.1528128]], PRIMEM["Greenwich", 0], UNIT["Degree", 0.0174532925199433]]',
    'metre',
    1
)

This gave another error: "Msg 259, Level 16, State 1, Line 13
Ad hoc updates to system catalogs are not allowed."

So, I looked how I could update a system catalog. It was explained on DBA how to do this. But it was stated there that there is a severe risk of breaking your MS SQL Server installation, and that warranty would cease. So, it seems that updating the system catalog directly is not really an option.

I first asked this question on Microsoft Docs but I was told to ask my question here.

Moreover, people seem to have asked similar questions:

How to add new spatial reference system (on SQLservercentral) asked in 2011, no response.
Insert new spatial reference system SRID into SQL Server (on stackoverflow) asked 2019, no response.

If you have an answer to my question, or have a different idea to get me to my end goal (stated above) please let me know.

Best Answer

I want to use Spatial Reference ID 28992, as found on EPSG, in MS SQL Server. How can I add this to MS SQL Server?

As you've mentioned, there are ways to modify system catalogs manually but in general this is not supported or recommended. As such, I consider this a non-option.

The documentation specifies:

SQL Server supports SRIDs based on the EPSG standards. A SQL Server-supported SRID for geography instances must be used when performing calculations or using methods with geography spatial data. The SRID must match one of the SRIDs displayed in the sys.spatial_reference_systems catalog view.

Emphasis mine.

So it seems to be clear that SQL Server does not support this particular SRID (as of SQL Server 2019 - db<>fiddle).

That being said, you should

Create a feedback item for the MSSQL team about adding this functionality.
Look into PostGIS which is widely considered the best option for handling GIS data in a relational database (and free, to boot).

Related Solutions

Sql-server – What does CREATE ASSEMBLY do with the registered dll

The bits are copied into the database where you call CREATE ASSEMBLY or ALTER ASSEMBLY.

The path you select in Management Studio is only a convenience for it to read the file and dump out the bits into a varbinary(MAX) value (hit the Script button in that dialog to see what I mean).

This also means that the assembly is "carried" with the database if you do something like backup/restore on another server.

Sql-server – How to inspect the security settings of a SQL Server Endpoint

After spending a portion of the day dealing with moving mirrored databases from server to server in our DEV environment in preparation for our move to SQL Server 2012, I came across this MSDN document that explains that ENDPOINT security is determined solely based on the type of account SQL Server is running under. If SQL Server is using a domain account, then that account automatically has access to the endpoint. If SQL Server is using a built-in account such as [Network Service] or [Local System] etc, then the endpoint must be configured with a security certificate, and only holders of the certificate can access the endpoint.

The relevant section from the document:

Determining the Authentication Type for a Database Mirroring Endpoint

It is important to understand that the SQL Server service accounts of your server instances determine what type of authentication you can use for your database mirroring endpoints, as follows:

If every server instance is running under a domain service account, you can use Windows Authentication for your database mirroring endpoints. If all the server instances run as the same domain user account, the correct user logins exist automatically in both master databases. This simplifies the security configuration for the availability databases and is recommended.

If any server instances that are hosting the availability replicas for an availability group run as different accounts, the login each account must be created in master on the other server instance. Then, that login must be granted CONNECT permissions to connect to the database mirroring endpoint of that server instance.

If your server instances use Windows Authentication, you can create database mirroring endpoints by using Transact-SQL, PowerShell, or the New Availability Group Wizard.

Note:

If a server instance that is to host an availability replica lacks a database mirroring endpoint, the New Availability Group Wizard can automatically create a database mirroring endpoint that uses Windows Authentication.

If any server instance is running under a built-in account, such as Local System, Local Service, or Network Service, or a nondomain account, you must use certificates for endpoint authentication. If you are using certificates for your database mirroring endpoints, your system administrator must configure each server instance to use certificates on both outbound and inbound connections.

There is no automated method for configuring database mirroring security using certificates. You will need to use either CREATE ENDPOINT Transact-SQL statement or the New-SqlHadrEndpoint PowerShell cmdlet. For more information, see CREATE ENDPOINT (Transact-SQL).

Remus Rusanu (in his answer) correctly identified how to determine the security in place on a mirroring endpoint; as in:

SELECT ep.name, sp2.name AS Grantee, sp.name AS Grantor, p.permission_name, ep.state_desc
FROM sys.server_permissions p 
    INNER JOIN sys.endpoints ep ON p.major_id = ep.endpoint_id
    INNER JOIN sys.server_principals sp ON p.grantor_principal_id = sp.principal_id
    INNER JOIN sys.server_principals sp2 ON p.grantee_principal_id = sp2.principal_id
WHERE p.class_desc = 'ENDPOINT' AND ep.type_desc = 'DATABASE_MIRRORING'

However in my case on SQL Server 2005 the original mirroring endpoints do not show up in the results of this query - I assume this is because they default security allows CONNECT access to the account SQL Server is running under (as explained above).

So the canonical answer to the question, how do you know who has CONNECT access to an ENDPOINT, is the sum of the query above, and the account SQL Server is using, along with any possible certificates you've configured with access to the endpoint.

Best Answer

Related Solutions

Sql-server – What does CREATE ASSEMBLY do with the registered dll

Sql-server – How to inspect the security settings of a SQL Server Endpoint

Related Question