0

I've scoured the internet and apparently in the Linux version of SQL Server 2019 linked servers are only allowed between 1 SQL Server to another SQL Server. I need to create a linked server from linux SQL Server 2019 to postgresql 4.17 so I can query tables that are in PostgreSQL from SQL Server.

I have ODBC drivers installed on my Windows machine that can connect and I have the odbc drivers installed on my ubuntu server that hosts SQL Server 2019. I can also connect and pull data and run queries from SSIS. OPENQUERY, OPENDATASOURCE and OPENROWSET do not work either.

I can't believe this is not possible. Any suggestions or ideas on how to do this or the closest best possible solution would be greatly appreciated.

Improve this question

1 Answer 1

Reset to default
1

Edit

Linux version of SQL Server 2019 only supports linked server connections to other SQL Servers.

https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-editions-and-components-2019?view=sql-server-linux-2017

I suggest looking at other ways for importing data, like BCP and SSIS, unfortunately.

End of edit

Your statement: "apparently in the Linux version of SQL Server 2019 linked servers are only allowed between 1 SQL Server to another SQL Server." Could you perhaps link to where that is stated in the Microsoft documents?

In the release notes for SQL Server 2019 on Linux I see mention of linked servers (with no statement that you can only connect to other SQL Servers).

Is there an error message you are receiving, or could you give us some more information on what steps you have taken and troubleshooting you've done? There are some configurations needed to insure communication on linux, according to the docs:

Features that involve outbound TCP connections from the sqlservr process, such as linked servers or Availability Groups, might not work if both the following conditions are met:

The target server is specified as a hostname and not an IP address.

The source instance has IPv6 disabled in the kernel. To verify if your system has IPv6 enabled in the kernel, all the following tests must pass:

  • cat /proc/cmdline will print the boot cmdline of the current kernel. The output must not contain ipv6.disable=1.
  • The /proc/sys/net/ipv6/ directory must exist.
  • A C program that calls socket(AF_INET6, SOCK_STREAM, IPPROTO_IP) should succeed - the syscall must return an fd != -1 and not fail with EAFNOSUPPORT. The exact error depends on the feature. For linked servers, this manifests as a login timeout error. For Availability Groups, the ALTER AVAILABILITY GROUP JOIN DDL on the secondary will fail after 5 minutes with a download configuration timeout error.

To work around this issue, do one of the following:

  • Use IPs instead of hostnames to specify the target of the TCP connection.

  • Enable IPv6 in the kernel by removing ipv6.disable=1 from the boot cmdline. The way to do this depends on the Linux distribution and the bootloader, such as grub. If you do want IPv6 to be disabled, you can still disable it by setting net.ipv6.conf.all.disable_ipv6 = 1 in the sysctl configuration (for example, /etc/sysctl.conf). This will still prevent the system's network adapter from getting an IPv6 address, but allow the sqlservr features to work.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I can't find the link where I read this but either way, When trying to create a linked server in Linux Sql Server in SSMS the other data source is greyed out. Only the the Sql Server option can be selected. So there is no error message.
from the command line running the following:<br/> SELECT a.*<br/> FROM OPENROWSET('MSDASQL', <br/> 'Driver=PostgreSQL AMD64A;uid=username;Server=IPAddress;port=5432;<br/>database=dbname;pwd=password',<br/> 'SELECT top 100 * FROM mytable')<br/> AS a;<br/> I get the following error:<br/> Msg 7222, Level 16, State 255, Line 44<br/> Only a SQL Server provider is allowed on this instance.<br/>
Found it, you are correct, unsupported for non-sql server connections. learn.microsoft.com/en-us/sql/linux/…
Thank you for your input. I will test this and let you know the outcome.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.