question

CourtneyHaedke-0265 avatar image
0 Votes"
CourtneyHaedke-0265 asked ShivendooKumar-8455 answered

Can't Connect to Azure Blobl Storage using polybase through Microsoft SQL Server 2017

Hello Polybase Support,

I am trying to connect to an external Polybase table on Microsoft SQL Server 2017 and every time I query the table I get the below error:

OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Login timeout expired".
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.".
Msg 10060, Level 16, State 1, Line 0
TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

Completion time: 2020-10-30T09:02:34.8292051-04:00
I researched some articles online and did the following steps
1)Created the Master Key to the database

2) Created Scoped Credentials to Blob storage

3) Set up CSV External Datasource

4) Created External Table then I trying to query the external table but getting the following error

OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Login timeout expired".
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.".
Msg 10060, Level 16, State 1, Line 0
TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

Completion time: 2020-10-30T09:02:34.8292051-04:00

I looked up how to fix this issue it was because the Polybase Engine wasn't running and TCP/IP needed to be enabled. So I went to the Microsoft configuration manager.

Started the Polybase engine rebooted my machine and I'm still getting the above error

I then read other instructions and enabled the firewall settings rebooted my machine and I'm still getting that connection error.
36365-firewall.jpg


Are there any perquisites I am missing?


sql-server-generalazure-blob-storage
firewall.jpg (21.6 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It could help if you posted the statements for CREATE EXTERNAL DATASOURCE and CREATE EXTERNAL TABLE that you used.

0 Votes 0 ·

I'm trying to create the external data source but I keep getting above error.

CREATE EXTERNAL DATA SOURCE output
WITH(TYPE = HADOOP , LOCATION = 'wasbs://output@misc.blob.core.windows.net/',CREDENTIAL = AzureStorageCredential)


0 Votes 0 ·
deherman-MSFT avatar image
0 Votes"
deherman-MSFT answered

@CourtneyHaedke-0265
I am unfamiliar with the SQL server side of things, so have added the tag to see if we can get more responses from some SQL experts. I wanted to see if you can confirm you can connect to the blob storage account from your server. Make sure that the firewall on the storage account allows access and is not blocking. In this external blog post they connected to the storage account from SQL Server Management Studio, perhaps this is something you could test.



Please don’t forget to "Accept the answer" and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered ErlandSommarskog commented

Hi @CourtneyHaedke-0265,

...I then read other instructions and enabled the firewall settings rebooted my machine and I'm still getting that connection error.Are there any perquisites I am missing?

This is one connection issue.Try steps as next:
1.Close the firewall and antivirus softwares;
2.Enable TCP/IP protocols both on server and client side;
3.Make sure the port 1433 is enabled.

Client side:
36590-20201102clienttcpport.jpg
Server side;
36692-20201102serversidetcpipport.jpg

More information: mssqlserver-10060-database-engine-error, troubleshoot-connecting-to-the-sql-server-database-engine,resolving-could-not-open-a-connection-to-sql-server-errors

BR,
Mia


If the answer is helpful, please click "Accept Answer" and upvote it.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This is one connection issue.Try steps as next:
1.Close the firewall and antivirus softwares;
2.Enable TCP/IP protocols both on server and client side;
3.Make sure the port 1433 is enabled.

Mia, in case you missed it, Countney is trying to access data in Hadoop, so port 1433 has nothing to do with it.

Really why the error message talks about SQLNCLI is not clear to me; it is certainly not used to access Hadoop. Then again, I've seen similar before when people have had problems with Polybase, a feature I'm not very good friend with myself.

0 Votes 0 ·
VaibhavChaudhari avatar image
0 Votes"
VaibhavChaudhari answered ErlandSommarskog commented

Could you check once again if TCP/IP in SQL configuration manager is enabled.

Also, try restarting the main SQL Server service and later ensure that both polybase services are also running

Was your create external data source query successful?


Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks getting back to me. I I made sure TCP/IP and restarted SQL Server Service.
I restarted the Polybase services too.
I ran the create data source query and now I'm getting the attached error. 37148-transportlevel.jpg


0 Votes 0 ·
transportlevel.jpg (22.5 KiB)

Certainty not a very helpful error.

What statement is on line 22?

In the SQL Server errorlog, are the any error messages that seems to be related to this error?

Polybase has its own logs, something of interest here?

What is the output of "SELECT @@version"?

0 Votes 0 ·
ShivendooKumar-8455 avatar image
0 Votes"
ShivendooKumar-8455 answered

I was getting this error message and was not sure but then I checked services and found that both the Polybase services were not running. Once I restarted, I was able to create

CREATE EXTERNAL DATA SOURCE ABCD
WITH (
TYPE = HADOOP,
LOCATION = 'wasbs://dwhbackup@XYZ.blob.core.windows.net/',
CREDENTIAL = RST
);

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.