Hello Everyone,
Kindly asking for solution on my Cloud SQL using SQL Server environment. Currently I have two environment:
- Testing Environment using Cloud with SQL Server 2019 Express
- Production Environment using Cloud with SQL Server 2019 Standard
From the GCP Documentation (https://cloud.google.com/sql/docs/sqlserver/manage-linked-servers),,) we try to create Linked Server with this query using SSMS (connecting from a Compute Engine’s VM - Window Server 2019 - that already been setup using associate VPC Network):
USE MASTER
GO
EXEC sp_addlinkedserver @server = N'loopback',@srvproduct = N' ',@provider =
N'SQLNCLI', @datasrc=@@SERVERNAME
GO
EXEC sp_serveroption loopback,N'remote proc transaction promotion','FALSE'
EXEC sp_serveroption loopback,N'RPC OUT','TRUE' -- Enable RPC to the givenserver.
GO
The query result seems to be okay. The in testing environment (SQL Server 2019 Express) seems to be okay when checking Server Objects > Linked Servers. But in production environment (SQL Server 2019 Standard), it seems to create an empty Linked Server Object with name “default” like picture below.
Production Environment (SQL Server 2019 Standard) :
In testing environment (SQL Server 2019 Express), it correctly displayed a database name.
Testing Environment (SQL Server 2019 Express) :
Furthermore, checking on the SQL Server Logs, we found SQL Server Error Code 18456:
12/08/2023 10:04:37,Logon,Unknown,Error: 18456 Severity: 14 State: 148.,
12/08/2023 10:04:37,Logon,Unknown,Login failed for user ‘9f8b9c7c800c751\9f8b9c7c800c751$’. Reason: Token-based server access validation failed with an infrastructure error. Login lacks connect endpoint permission. [CLIENT: 127.0.0.1],
So currently I can not use properly Linked Server for my production environment (SQL Server 2019 Standard), and wonder why got an error in this environment while not in the testing environment (SQL Server 2019 Express). Do you guys know a solution for this?
Additional:
I also try something by checking sys.endpoints (select * from sys.endpoints).
It seems there is differences status for “TSQL Default TCP” endpoint , when creating Cloud SQL with SQL Server. In SQL Server Express the state will be running but in the SQL Server Standard the state will be stopped.
Furthermore I try checking the endpoint permission using query:
SELECT * FROM sys.server_permissions AS sp2
JOIN sys.server_principals AS sp
ON sp2.grantee_principal_id = sp.principal_id
LEFT OUTER JOIN sys.endpoints AS e
on sp2.major_id = e.endpoint_id
WHERE sp2.permission_name = 'CONNECT'
AND sp2.class_desc = 'ENDPOINT'
In production environment (SQL Server 2019 Standard), the state of “TSQL Default TCP” endpoint is “D” / Deny: (But in Testing environment or SQL Server 2019 Express it is “G” for “Grante”)
I try to find solution and found two forums:
- https://groups.google.com/g/google-cloud-sql-discuss/c/KdGeiWgkY8U
- https://dba.stackexchange.com/questions/214374/login-lacks-connect-endpoint-permission-in-sql-server-error-18456-state-149
Suggesting me to grant CONNECT permission on my Cloud SQL Endpoint (TSQL Default TCP) to public.
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public;
But when I execute it I also got error that maybe related to permission of Cloud SQL default user:
Msg 15151, Level 16, State 1, Line 25
Cannot find the endpoint ‘TSQL Default TCP’, because it does not exist or you do not have permission.
What i know in Cloud SQL for SQL Server the default user created “sqlserver” is part of the CustomerDbRootRole role. But I don’t know why can not execute the Grant query above.
Kindly any advise or comment will be welcomed, thank you.
