We have seen a number of PolyBase cases in SQL Server Support related to logins or permissions created and set during installation that are missing. It is quite common for organizations to want to lock down their environment from a security perspective in order to comply with principal of least privilege. Unfortunately, this has resulted in the removal of logins and permissions that are required for PolyBase to function properly.
I want to give a brief overview of the logins and permissions that are required and then highlight a couple of the common issues we've seen when these logins and permissions are removed.
Permissions Assigned During Installation
When you install SQL Server 2019 with PolyBase feature you must assign the service account for the two PolyBase services (PolyBase Engine, PolyBase Data Movement). The default account is NT AUTHORITY\NETWORK SERVICE. If you wish to use PolyBase scale-out groups, you must use a domain account. For the purposes of this post, I did a fresh installation of SQL Server 2019 with PolyBase feature and used the account 'SQLPB\PolyBaseSvcAcct' for the services.
It's important to point out that there are three databases that get created when installing the PolyBase feature:
- DWConfiguration
- DWDiagnostics
- DWQueue
The specific ways these databases function in PolyBase are outside the scope of this post, but in order for PolyBase to function properly, the service account must have permissions within these databases. The needed permissions are achieved by database role membership assigned to the service account.
After installation, you'll see that the domain login is added to SQL Server, but does not have sysadmin role (or any server role, aside from public).
You'll also see that the account has the following database roles in the previously mentioned databases:
{DWConfiguration}
- db_datareader
- db_datawriter
- public
{DWDiagnostics}
- db_datareader
- db_datawriter
- db_ddladmin
- public
{DWQueue}
- db_datareader
- db_datawriter
- public
{tempdb}
After troubleshooting some of the issues I'll mention later, I learned that there are two certificates (_##PDW_SmDetachSigningCertificate## and _##PDW_PolyBaseAuthorizeSigningCertificate##) created in master database and two logins (l_certSignSmDetach and l_certSignPolyBaseAuthorize) that are also created during installation of the PolyBase feature. These security objects help maintain the principals of least privilege for the service accounts while facilitating some of the PolyBase functionality that requires sysadmin role on the SQL Server instance. During installation, the certificates are created and then the logins are created from the certificates. In short, these two logins must exist and must be members of the sysadmin server role. If they do not exist or are missing sysadmin role membership, you may see issues I outline below.
Common Issues
Data Movement Service won't start - Shared memory segment has not been created or attached
Symptom
SQL Server PolyBase Data Movement service fails to start. If you're starting it from SQL Server Configuration Manager, you'll see that it enters the Running state, but after a short time, it is no longer running. If you check the Dms_errors.log file in the PolyBase logs directory (default instance: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\Polybase), you'll see:
8/14/2020 1:07:52 PM [Thread:2724] [CommandManager:ErrorEvent] (Error, High):
Microsoft.SqlServer.DataWarehouse.DataMovement.SharedMemory.NativeSharedMemoryException: Shared memory segment has not been created or attached.
at Microsoft.SqlServer.DataWarehouse.DataMovement.SharedMemory.NativeSharedMemory.ThrowIfInvalid()
at Microsoft.SqlServer.DataWarehouse.DataMovement.SharedMemory.NativeSharedMemory.SetDistributionCount(Int32 distrCount)
at Microsoft.SqlServer.DataWarehouse.DataMovement.Nodes.DMSChannelMap.UpdateSharedMemoryDistributionCount()
at Microsoft.SqlServer.DataWarehouse.DataMovement.Nodes.DMSChannelMap.AddDataChannelReceiver(String nodeName, DataChannelReceiver dataChannelReceiver)
at Microsoft.SqlServer.DataWarehouse.DataMovement.CommandManager.StartDataChannel(Int32 senderQueueSize, Int32 tcpBufferSize, HashSet`1 nodesToConnect)
at Microsoft.SqlServer.DataWarehouse.DataMovement.CommandManager.ProcessStartDataChannel(IMessage message)
at Microsoft.SqlServer.DataWarehouse.DataMovement.CommandManager.ProcessBaseMessage(IBaseMessage message)
at Microsoft.SqlServer.DataWarehouse.DataMovement.CommandManager.Run()
at Microsoft.SqlServer.DataWarehouse.Common.ThreadManager.ThreadFactory.ExecuteCallback(Delegate callback, Object state, Boolean throwException, String name)
If you capture a profiler trace, you'll find the following Profiler events:
SP:Starting - exec DWConfiguration.dbo.sp_pdw_sm_detach N'Global\{GUID}'
SP:StmtStarting - EXEC [sp_sm_detach] @FileName
User Error Message - Only sysadmin can execute this stored procedure 'sp_sm_detach'.
Cause
This occurs because either the l_certSignSmDetach login doesn't exist or it isn't a member of the sysadmin server role.
Resolution
If the login is missing, recreate it from certificate.
CREATE LOGIN [l_certSignSmDetach] FROM CERTIFICATE [_##PDW_SmDetachSigningCertificate##]
GO
If login exists, but doesn't have sysadmin role, add it
ALTER SERVER ROLE [sysadmin] ADD MEMBER [l_certSignSmDetach]
GO
Windows authentication failed. Possible network configuration issue. Please contact your system administrator.
Symptom
Executing a query to an external table gives the following error:
Msg 110064, Level 16, State 1, Line 36
110064;Windows authentication failed. Possible network configuration issue. Please contact your system administrator.
If you execute a query to any of the PolyBase DMVs, you'll see the following (slightly different) error:
Msg 44528, Level 16, State 1, Line 1
110064;Windows authentication failed. Possible network configuration issue. Please contact your system administrator.
If you check the DWEngine_errors.log file, you'll find:
8/14/2020 1:48:05 PM [Thread:5340] [TdsServiceClient:ErrorEvent] (Error, High):
Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.LoginFailedException[110064:1]: 110064;Windows authentication failed. Possible network configuration issue. Please contact your system administrator. ---> Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.MppSqlException[110064:1]: 110064;Windows authentication failed. Possible network configuration issue. Please contact your system administrator. ---> Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.MppSqlException[110064:1]: 110064;Windows authentication failed. Possible network configuration issue. Please contact your system administrator. ---> System.Data.SqlClient.SqlException[40104:1]: Only sysadmin can execute this stored procedure 'sp_polybase_authorize'.
If you gather a profiler trace during the attempted query execution, you'll see:
SP:Starting - exec DWConfiguration.dbo.sp_pdw_polybase_authorize @AppName=N'6CF2B44E-A0CF-53B2-5245-F6F7995AEE0B'
SP:StmtStarting - EXEC [sp_polybase_authorize] @AppName
User Error Message - Only sysadmin can execute this stored procedure 'sp_polybase_authorize'.
Cause
This occurs because either the l_certSignPolyBaseAuthorize login doesn't exist or it isn't a member of the sysadmin server role.
Resolution
If the login is missing, recreate it from certificate.
CREATE LOGIN [l_certSignPolyBaseAuthorize] FROM CERTIFICATE [_##PDW_PolyBaseAuthorizeSigningCertificate##]
GO
If login exists but doesn't have sysadmin role, add it
ALTER SERVER ROLE [sysadmin] ADD MEMBER [l_certSignPolyBaseAuthorize]
GO
