I have a SQL Server 2008R2, and I want to connect remotely to a SQL Server 2012SP1, for which I am given:
IP: 184.108.40.206 Login: john Password: pw1 Database: TESTDB Table: test_table
The remote database is outside my LAN, but firewall is configured on both sides, so I can query it by opening SSMS directly from my database server. I perform the following:
EXEC sp_addlinkedserver '220.127.116.11', 'SQL Server'; EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = [18.104.22.168], @locallogin = NULL , @useself = N'False', @rmtuser = 'john', @rmtpassword = 'pw1'
Up to now, no error, and I can see the linked server in SSMS, "Test Connection" is successful but I cannot query it.
select * from [22.214.171.124].TESTDB.test_table
Returns this error:
Msg 208, Level 16, State 1, Line 1 Invalid object name '126.96.36.199.TESTDB.test_table'.
What is the proper way to define a linked server from the Internet, and if possible assigning a nice name to it (rather than referring to it by IP)? Should I be using SQLNCLI as a provider?