search

microsoft / go-sqlcmd

The new sqlcmd, CLI for SQL Server and Azure SQL (winget install sqlcmd / sqlcmd create mssql / sqlcmd open ads)
https://learn.microsoft.com/sql/tools/sqlcmd/go-sqlcmd-utility
MIT License
323 stars 56 forks source link

sqlcmd behind http_proxy #484

Closed e-bits closed 6 months ago

e-bits commented 6 months ago

I'm currently struggeling setting up a working connection with sqlcmd to an MSSQL Database hosted on Azure.

My main goal is to execute a file containing different SQL queries query.sql during a deployment pipeline. If I run the the following command on my development Machine everything is working fine. That's because there is no http_proxy in place. The exact same command is failing on our pipeline runners which are covered with a http_proxy.

./sqlcmd -S tcp:server-name.database.windows.net:1433 -d db-name -i query.sql

So I started messing around with nc and ncat but without any luck.

ncat --sh-exec "ncat --ssl server-name.database.windows.net 1433 --proxy-type http --proxy proxy-server:3128" -l 14333 --keep-open -vvv then try to connect with: ./sqlcmd -S tcp:localhost:14333 -d db-name -i query.sql But this results in mssql: login error: Cannot open server "localhost" requested by the login. The login failed.

Im sure the credentials are correct and working. By the way username and password are comming from these to ENV's:

All the commands and tests are done within a debian:bookworm container

Is anybody facing the same issue? or has an working approch to get trough a http_proxy? Or would it be possible to make go-sqlcmd proxyaware?

Thanks Cheers e-bits

shueybubbles commented 6 months ago

Do you have any example applications that talk to Azure SQL DB through this same proxy? The TDS protocol uses tcp but not http so I'm not sure what the expectation is here. I am not a networking expert at all, though.

e-bits commented 6 months ago

Do you have any example applications that talk to Azure SQL DB through this same proxy? The TDS protocol uses tcp but not http so I'm not sure what the expectation is here. I am not a networking expert at all, though.

No I did not found any application talking to Azure SQL DB through this proxy. If for example I would like to connect to an SSH Server from this same environment (behind proxy) I need to configure this: "ProxyCommand nc -X connect -x $PROXY_IP:$PROXY_PORT %h %p" >> ~/.ssh/config

You're probably pointing me into right direction. How to check if our proxy is also handling tcp traffic?

shueybubbles commented 6 months ago

afaik you have to get your proxy owner to let port 1433 requests through instead of being redirected by the proxy. The server requires the name in the connection string match the DNS name of the server too.

e-bits commented 6 months ago

Hi @shueybubbles,

thanks for your help. I finally got it working with help of this comment: https://github.com/dbeaver/dbeaver/issues/4741#issuecomment-605396006

It looks like if you're going through a tunnel you need to specify the username like this: export SQLCMDUSER=user@serverFQDN

If someone else finds this issue because of tunneling go-sqlcmd through a proxy this is the ncat command I used: ncat --sh-exec "ncat --ssl $SQLCMDSERVER 1433 --proxy-type http --proxy $PROXY_IP:$PROXY_PORT" -l 14333 --keep-open &

Afterwards you should be able to connect like this: ./sqlcmd -S tcp:localhost:14333 -d $SQLCMDDATABASE -i query.sql

Envrionments Variables which need to be set: