Unable to create on premise gateway to sql server on my laptop

[bonseye]

Cannot create connection to on premise sql server on my laptop using pipeline. Connection works via Gen 2 data flow connection works, but not as a pipeline. This is the error I get: 

mssqlserver_201601_gateway: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Please have this information handy if you choose to create a support ticket. Session Id: f7a58f38-d378-4dc8-924a-5d2a67ad165e RequestId: fedeed77-f49e-445f-9c8e-a2f1b12c7310 Cluster URI: https://api.powerbi.com Status code: 400 Time: Mon Mar 24 2025 16:25:00 GMT-0500 (Central Daylight Time)

Login: 

localhost\1433

Authentication method: basic

Username/password: Works in SSMS 

Single Sign on: no boxes checked. 

General: Privacy Level: Organizational.

Have configured: 

Server to allow remote connections. Confirmed via cmd as well.

Enabled TCP/IP

Created inbound and outbound rules to allow communication for port 1433 through windows firewall

Also have tried all suggestions in this similar post: https://community.fabric.microsoft.com/t5/Data-Engineering/On-premises-Gateway-for-SQL-Server-Instan... 

Any ideas appreciated!

[bonseye · Accepted solution]

I never received a notification that the connection was successful when creating the connection via the connections admin portal, but I noticed a long list of existing connections in the admin portal. Looked through the list and found connections to the on prem server I have been attempting to connect to.   These connections were not assigned a "connection name" which is a required field when creating a new connection in the admin portal, so I cannot say for sure these were created through my attempts.  I used these connections in a new pipeline and it worked.

I cannot explain the fix other than even though you get an error, check if you see the database in the list of connections, I guess? :).   Best of luck to all!

[kofi_smarte1]

Here is the screenshot of my error. Could someone help with the solution?

[lbendlin]

Your gateway has no idea what "localhost"  means.  Use the FQDN. No need to specify the port if you use the default one. But if you want, use a comma.  server.domain.com,1433  .

Running a SQL Server on your laptop is ... uhm ... suboptimal.

[DragonflyB]

Thank you for your response. I have also tried my computer name and got the same error.

Today, I ran a few commands on power shell to confirm my ip address and domain. I work from home. I do not have a domain so only have an ip address. For server, I tried with the ip address alone and also with the port number. This is the new error I get. 

Unable to create connection for the following reason: Logging into Sql server failed. Possible reasons for this error include an invalid authentication mode, a missing login in Sql server, a login from an untrusted domain or a problem contacting the domain controller for the authentication request.



Details: mssqlserver_201601_gateway: Received error payload from gateway service with ID 1621121: SqlException encountered while accessing the target data source.