The Enable Enterprise Geodatabase tool fails on an Azure SQL database managed instance with Azure AD authenticated SDE user with the error message, "Failed to connect. ODBC 17 error: 40532, [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot open server "database.windows.net" requested by the login. The login failed." returned.

参考情報

対処法

1) Enable the geodatabase with a database-authenticated sde user.

2) In SSMS, Run the following select statement to capture a series of grant statements for use in a later step.

SELECT 'GRANT ' + permission_name COLLATE DATABASE_DEFAULT

+ ' ON ' + user_name(o.schema_id) + '.' + o.name + ' TO '

+ user_name(p.grantee_principal_id) COLLATE DATABASE_DEFAULT + CASE

WHEN STATE = 'W'

THEN ' WITH GRANT OPTION'

WHEN STATE = 'G'

THEN ''

END

FROM sys.database_permissions p

JOIN sys.objects o ON p.major_id = o.object_id

where user_name(p.grantee_principal_id) = 'PUBLIC'

order by o.name

3) Now drop the database authenticated sde user and create an AAD authenticated sde user by executing these commands:

ALTER AUTHORIZATION ON SCHEMA::sde TO dbo

DROP USER sde

CREATE USER sde FROM LOGIN AAD login

ALTER AUTHORIZATION ON SCHEMA::sde TO sde

EXEC sp_addrolemember 'db_owner', 'sde';

Transferring ownership of the sde schema to the dbo user broke the permissions granted to the sde and the public role.

4) Fix the sde user permissions with the following grant statements:

grant create table to sde

grant create view to sde

grant create procedure to sde

grant create function to sde

5) Now fix the public role by running the grant statements collected in Step 2.

再現の手順