Is the username in your SQL Server database in capital, mixed mode letters or small letters?
Currently you have created a database link that tries to authenticate at the SQL Server using SQLUSER in capital letters. If you're using mixed or small letters, then make sure to write the username (and password) as it is in the create database link statement and surround it by double quotes.
If you created the database link correctly, try an ODBC connection for example from your client and add username and password. Does that work?
Thanks - I hadn't used " in the create syntax for the database link however this hasn't work when trying it.
I get the same error.
Do you know if SQL2008 R2 is supported?
Yes it works. Did you check if you can connect with ODBC or any other MS tool to the SQl Server using uid/pwd authentication instead of Windows NT authentication?
Sorry - yes I did and that didn't work either.
To confirm, my ODBC connection was attempted using the SQL server driver (I think that's what you were asking?)- I actually get a SQL Server does not exist or access denied message from that; but I can connect to OK from the SQL server box itself using an ODBC connection.
1 person found this helpful
I assume for the local connection ODBC connection you've also used the username/password method. So either there's a Firewall between your client and SQL Server that blocks the connections or the SQL Browser service might not be running (is needed when using instance connect method). Try using host and port instead.
Just to update this - I have now got this working, thanks to your assistance above.
We need to:
Use port number in the HS_FDS_CONNECT_INFO, removing the instance name in addition, as the port gets you straight to the instance (so to speak).
In addition, we checked the port configuration settings for the SQL instance, to ensure that the port was specified across all IP types (e.g. IPv4, IPv6 etc etc).
Restarted the gateway listener, recreated the database link using " for the user name and connectivity was restored!!!
Thank you very much kgronau!