Create external table from a JOB with Polybase

Question

I'm trying to run a create external table statement from a job in SQL Server, using Polybase.
The owner of the job is a windows user (not sysadmin) with the necessary permissions as described in https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-ver16&tabs=dedicated

The statement is called via a stored procedure. If I run the same statement using SSMS, it works. But when doing so from the job, the step does not fail but neither creates the external table. The output says:

Executed as user: *****************. TCP Provider: No such host is known. [SQLSTATE 42000] (Error 11001) OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412) OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Error 7412). The step failed.

Answer 1

Hi @Anonymous ,
Now that you can use the statement by SSMS and you have some problems when doing from the job. So I think the network may be the causing. In SQL Server Configuration Manager, make sure that TCP/IP is enabled for the Network protocol. If you turn on the Firewall, make sure the TCP port 1433 is in the Firewall exception list.
And you can use the following statements to check the connecting situation of your SQL.

USE master  
GO  
xp_readerrorlog 0, 1, N'Server is listening on'   
GO  

I'm not sure this can solve your problem. If this doesn't work, I will think of other ways to solve this error.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 2

I don't think it is a good idea to have a different owner than sysadmin. And for that matter, nor is it a good idea to have sysadmin as owner. It's simply not a good idea to have T-SQL job step when you have access to external data sources, since Windows authentication is not going to work.

When you use an different job owner, SQL Server Agent impersonates that login, but impersonation is only valid inside SQL Server. When you are sysadmin, the service account for SQL Server Agents to have access on the other side. Which is also unlikely.

Instead should you run this a CmdExec job step where you invoke SQLCMD to run the script. With a CmdExec job step you can use a proxy and there will be a real login which is valid in all contexts. I've written more details about this here: https://www.sommarskog.se/perm-hijack.html#agentjobs

Answer 3

Hi @Anonymous ,
Now we can know that it is not a connection problem. Have you tried the T-SQL sp_configure like this:

USE AdventureWorks2012 ;    
GO    
EXEC sp_configure 'remote query timeout', 0 ;    
GO    
RECONFIGURE ;    
GO    

The explaination is in this official article :https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option?view=sql-server-ver15

The soultions I have posted are the common and easy ones, if this one still doesn't work we may try focusing on OLE DB and accout setting, which maybe complicated.......

Answer 4

Your answers and tips are very much appreciated!
The option you suggest @Erland Sommarskog is not valid for us because we need a non sysadmin user as the owner of the job, to allow "unprivileged users" to do any future modifications of the job.
The guide for setting the proxy and running a CmdExec says that:
*

On the first page, set sa or some other non-person who is sysadmin as the job owner.