April 4, 2012 at 6:32 pm
I am creating a linked server from 2000 to 2008 R2. Both servers use different ports. Would this cause it to fail?
I'm having some issues trying to get it to work...
TIA
April 5, 2012 at 7:38 am
Is SQLBrowser running?
You may need to specify the port in the connection.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 5, 2012 at 7:42 am
Hi,
You can try to configure the Linked Server with alias name. This alias name will be given, when the two Servers are in Different Domain.
Please try to set up to configure the Linked Server as shwon in the below Link (third image)
April 5, 2012 at 7:53 am
I believe you can accomplish this by setting up an ODBC connection on the server itself (Administrator Tools > Data Sources (ODBC), create a System DSN, Choose your driver and configure it. Test your Connection. Then go to SQL Server and create the linked server, referencing the System DSN you just created
EXEC master.dbo.sp_addlinkedserver @server = N'REPORTING',
@srvproduct=N'MYSERVER',
@provider=N'SQLNCLI',
@datasrc=N'MYSERVER'
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 5, 2012 at 1:52 pm
Thank you everyone for the responses.
Bob I used your method (both servers are on the same domain) and it worked.
Quick question: When I browse the tree view in EM of my linked server it only shows System Catalogs. Should it in fact show the database I have mapped to the user I'm connected as?
April 5, 2012 at 2:20 pm
that would depend on the type of security you have defined on the linked server.
If you just ran the statement as is then your security context is set as "Be made using the Login's current Security context". If you are logged in to the local server using widows auth and and your account also has permissions on the remote server then you should be able to see any databases that your account has access to on the remote server in the tree view.
are you able to query the desired database(s) using the linked server? if not and you get an error something like
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
then you have probably have kerberos issues.
April 5, 2012 at 3:00 pm
I have SA privileges on both servers. If I setup a login using my domain username and choose impersonate the query I run thru EM fails.
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
If I setup a login using my domain username and a remote user on that Linked server the query works.
Ideally I wanted to setup two identical users on both servers and specify that as the login in the linked server. That doesn't seem to work either though.
Really though an application we have that has an SQL login will be running this query so I guess I should setup that login in the linked server too.
Sorry for the long reply but I've never done this before.
Thanks.
*Actually I got the identical users to work. Logged in as the one and rant they query.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply