October 8, 2010 at 6:38 am
I need to link to a server that is in a different domain (a Polyserve environment/domain if anyone knows what that is). I have it successfully setup for another server that's in the same domain, but I can't get it to work for the one not in the same domain.
Here's what I tried (this one works):
exec sp_addlinkedserver 'labdevrpt'
Select * From Openquery(labdevrpt, 'select top 100 * from iarts..cspeed')
This one doesn't:
exec sp_addlinkedserver '172.29.254.204,40002'
or
exec sp_addlinkedserver 'PSMVPROD5'
When I view the properties of the server, it says that the name is: PSMVPROD5\MVPROD5, I'm wondering if the slash in that name is because of the Polyserve environment.
When I try that second version (PSMVPROD5), it tells me:
OLE DB provider "SQLNCLI" for linked server "PSMVPROD5" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "PSMVPROD5" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections."."
When I try with the IP address, it says:
"Incorrect syntax near '172.29.254.204,40002'."
Anyone have any suggestions?
Thanks
October 8, 2010 at 6:49 am
for servers on a different domain(assuming you can connect), i've always used this format, which makes the alis for the server different from the connection:
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer',@srvproduct = N'', @datasrc = N'192.168.1.50,44334', @provider = N'SQLOLEDB';
-- Add Default Login (if applicable)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyLinkedServer',
@useself = N'False',
@locallogin = NULL,
@rmtuser = N'sa',
@rmtpassword = 'NotARealPassword';
Lowell
October 8, 2010 at 6:56 am
Thanks Lowell, I'm getting closer.
Tried running it without the login and it told me the login failed, or something like that. So I then ran the login part and now it says:
"Login failed for user 'ganders'. The user is not associated with a trusted SQL Server connection."
I think I need to make sure my username exists in that domain...(Polyserve is probably the cause of this error).
(I LOVE THIS SITE)
October 8, 2010 at 7:37 am
I'm under the impression that for a linked server on another domain, you must use a SQL username and password, and not a your domain credentials;
to use your domain credentials, you have to establish a trust between the two domains.
I'd have to ask my network guys how that is done, i just know what the issue is, but not how to resolve it.
Lowell
October 8, 2010 at 7:42 am
Funny thing is, I DID specify my username and password, but apparently I don't have the SQL username and password created. Hopefully it's just that easy. I know ALL of our other databases are configured that way, but the DBA/Network team said that that wouldn't be necessary with this new "Polyserve" domain because everything would be "trusted"...I'm guessing they didn't plan on needing a linked server setup.
Thanks again.
October 8, 2010 at 10:01 am
Hmm, had the DBA over, we added my username as a SQL Server login, and also as a Windows Authentication/trusted username...nothing works.
October 8, 2010 at 10:09 am
Here's my setup:
My PC is in domain 'A'
Primary SQL Server is in domain 'B'
Linked SQL Server is in domain 'C'
Domain C does not have any local logins, it has a "trust" with domain A.
I open Mgmt Studio from my PC (which sits on domain A)
Connect to Primary SQL Server using SQL authentication (NOT windows)
Anything wrong with this setup.
Here's the syntax that I'm using for adding the linked server:
EXEC sp_addlinkedserver @server = N'PSMVPROD5',@srvproduct = N'', @datasrc = N'172.29.254.204,40002', @provider = N'SQLOLEDB'
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'PSMVPROD5', @useself = N'True', @locallogin = 'B\ganders', @rmtuser = 'ganders'
Also have tried this:
EXEC sp_addlinkedserver @server = N'PSMVPROD5',@srvproduct = N'', @datasrc = N'172.29.254.204,40002', @provider = N'SQLOLEDB'
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'PSMVPROD5', @useself = N'True', @locallogin = 'A\ganders', @rmtuser = 'ganders'
And:
EXEC sp_addlinkedserver @server = N'PSMVPROD5',@srvproduct = N'', @datasrc = N'172.29.254.204,40002', @provider = N'SQLOLEDB'
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'PSMVPROD5', @useself = N'True', @locallogin = 'ganders', @rmtuser = 'ganders'
I've tried about every combination of having the domain in front of the username, locallogin true and false, all of them give the same error saying it's not a trusted login.
October 8, 2010 at 10:26 am
the piece you seem to be missing is the second part of my example...sp_addlinkedsrvlogin , which adding a link between you/yourlogin and what will be used on the remote server.
so for example, my snippet here says "if lowell is connected with his domain password, use sa/NotARealPassword for the credentials on the remote server.
if you do not add a linked login, your domain credentials get passed, and they are not valid on Domain "B"; you said yourself you are using a SQL login to get to Domain"B"'s server.
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'MyLinkedServer',
--if this is NULL, then anyone connecting to the linked server would use the 'sa' credentials in this setup:.
@locallogin = N'MyDomain\lowell',
@useself = N'False',
@rmtuser = N'sa',
@rmtpassword = N'NotARealPassword'
GO
--yours would look similar to this:
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'PSMVPROD5',
--if this is NULL, then anyone connecting to the linked server would use the 'sa' credentials in this setup:.
@locallogin = N'domainA\gregory.anderson',
@useself = N'False',
@rmtuser = N'sa',
@rmtpassword = N'NotARealPassword'
GO
Lowell
October 8, 2010 at 10:57 am
Hi Lowell, I haven't read through all of your response yet, but I got it to work. The problem was starting from domain A. I went to the Sql Server and logged into mgmt studio from there (domain B) and used windows authentication, then ran these scripts and it worked:
EXEC sp_addlinkedserver @server = N'PSMVPROD5',@srvproduct = N'', @datasrc = N'172.29.254.204,40002', @provider = N'SQLOLEDB'
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'PSMVPROD5', @useself = N'True', @locallogin = 'TESTDMZ\ganders', @rmtuser = 'ganders'
Select * From OpenQuery(PSMVPROD5, 'select top 100 * From RDARS..MDRVIN')
Thank you for your continued help with this. So glad this website exists!
October 8, 2010 at 11:01 am
Lowell (10/8/2010)
the piece you seem to be missing is the second part of my example...sp_addlinkedsrvlogin , which adding a link between you/yourlogin and what will be used on the remote server.so for example, my snippet here says "if lowell is connected with his domain password, use sa/NotARealPassword for the credentials on the remote server.
if you do not add a linked login, your domain credentials get passed, and they are not valid on Domain "B"; you said yourself you are using a SQL login to get to Domain"B"'s server.
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'MyLinkedServer',
--if this is NULL, then anyone connecting to the linked server would use the 'sa' credentials in this setup:.
@locallogin = N'MyDomain\lowell',
@useself = N'False',
@rmtuser = N'sa',
@rmtpassword = N'NotARealPassword'
GO
--yours would look similar to this:
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'PSMVPROD5',
--if this is NULL, then anyone connecting to the linked server would use the 'sa' credentials in this setup:.
@locallogin = N'domainA\gregory.anderson',
@useself = N'False',
@rmtuser = N'sa',
@rmtpassword = N'NotARealPassword'
GO
After reading that, and getting it to work, at least from Mgmt Studio, I have a few more questions on how to setup for a real application.
My application runs from domain B, with a username of 'appusername'. When I add our application username to the linked server, how would that go? How does locallogin relate in this scenario? Do I need to specify the rmtuser/password when adding the application username?
October 12, 2010 at 6:48 am
Hey, I'm back. I started a new topic on this next question I have, but forgot that I had this post going already, basically the same problem.
I've now gotten the linked server setup working on my local development environment, but now that I've promoted my code to our UAT environment, the username isn't quite working. I'll paste what I had in the other post:
My background:
Application running on Windows 2003 server
ApplicationPool username is 'dotnetsysadmin'
My application opens a web browser which is an aspx page (I bring that up because I'm not sure if the app would be sending the "System.Current.Username", or whatever it is, or if it's 'dotnetsysadmin'
I've run these different versions of the sp_addlinkedsrvlogin script:
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'labdevrpt', @useself = N'True', @locallogin = 'IDOT\dotnetsysadmin', @rmtuser = 'dotnetsysadmin'
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'labdevrpt', @useself = N'False', @locallogin = 'IDOT\dotnetsysadmin', @rmtuser = 'dotnetsysadmin'
For the first of those, I get this error in my application:
An error occurred trying to retrieve the RDARS data. Check the ExceptionLog for more details. A SqlClient.SqlException happened in ExecuteFill! Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
And for the second, I get this error:
An error occurred trying to retrieve the RDARS data. Check the ExceptionLog for more details. A SqlClient.SqlException happened in ExecuteFill! Login failed for user 'dotnetsysadmin'.
BTW - I've sent in a request to our DBA to run the 2nd version of that script while defining the username AND password, because our server team won't let us know what the application password is...
Any other help that I can get with this would be great. Thanks!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply