March 21, 2011 at 4:45 pm
Hi there
I am quite new to this so bear with me. My setup
I have SQL Server Express 2008R2 installed locally
I use a VPN to connect to a box on Rackspace where a number of db's are held. Once I connect I can access the different databases via their local IP's
I have setup a connection to a MSSQL database successfully and can query it
I have setup a linked server to a MySQL database successfully and can query it
What I can't do is create a join between the 2. I kind of know what the problem is but can't seem to resolve it and I think it is probably quite easy
I can see towards the bottom of the window in SQL Server Mgmt Studio which database I'm connected to.
So when I am connected to the local (it shows in yellow), I can query the linked server (since this is where the linked server is set up) by using dbname...tablename
When i am connected to the MSSQL db (again shown in yellow) I can connect to the db using dbname..tablesname. But when I am connected to the local I can't connect to the MSSQL database - I've read that I might need to put in the 4 part address - I've tried all combinations but nothing works.
The server name for the MSSQL connection is 192.168.100.11 which I've tried with brackets, without etc
Can anyone help? What am I missing? Do I need to 'use' this database as part of the query?
Thanks in Advance
March 22, 2011 at 9:25 pm
OK - thanks for the advice.
I've tried to set up the linked server for the MSSQL database I want to access. I created an ODBC connection and can successfully connect when I test this. When I then create the linked server using this connection, it says the authentication failed.
TITLE: Microsoft SQL Server Management Studio
------------------------------
The linked server has been created but failed a connection test. Do you want to keep the linked server?
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The OLE DB provider "SQLNCLI10" for linked server "PAYCENTRE" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "PAYCENTRE".
OLE DB provider "SQLNCLI10" for linked server "PAYCENTRE" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)
What does this mean?
Thanks
March 23, 2011 at 2:09 am
Hey - thanks for the reply. I selected the 'Be made using the logins current security context'. I wasn't sure what you meant about impersonate though. Could you explain more?
That error has now disappeared but I now have another
TITLE: Microsoft SQL Server Management Studio
------------------------------
The linked server has been created but failed a connection test. Do you want to keep the linked server?
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
OLE DB provider "SQLNCLI10" for linked server "PAYCENTRE" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "PAYCENTRE" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 10060)
I can connect to the database directly through the database engine connection but just not via a linked server.
Any help is greatly appreciated
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply