November 30, 2011 at 10:25 am
I just used the same username and password that I am using in the provider string in "Be made using this security context". Still no luck.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 30, 2011 at 10:26 am
Well... I am out of ideas. Sorry!
Jared
Jared
CE - Microsoft
November 30, 2011 at 10:31 am
I just noticed you said you're having this issue only when connecting from a remote location. I assume you're on a VPN connection of some sort? In any event, try using a SQL server user/pwd combination for the linked server server security set up. There may be some issue with the windows authentication to the linked server across the remote connection.
Also, the SQL Server account that you use, must be set up on the remote server in order for that to work...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 30, 2011 at 10:39 am
When I say remote connection I am using SSMS to connect to DB Server from my laptop instead of connecting from the SSMS from the DB Server directly. I am not using VPN as I am in the same location where the DB Server is located.
Currently, I am using my windows authentication to connect to the DB Server and I am the sysadmin. I will try creating a sql login on the DB Server with full permissions and test to see if windows authentication is an issue here.
Also, the SQL Server account that you use, must be set up on the remote server in order for that to work...
do you mean on the mas 90 providex database??
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 30, 2011 at 11:38 am
>> Sapen, Yes...it must match the credentials on the remove server (mas 90 providex database)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 30, 2011 at 11:54 am
MyDoggieJessie (11/30/2011)
>> Sapen, Yes...it must match the credentials on the remove server (mas 90 providex database)
Really? I have linked servers set up with users that do not exist on the source server. Isn't the source simply passing whatever credentials you tell it to?
Jared
Jared
CE - Microsoft
November 30, 2011 at 11:59 am
I am not much familiar with adding users to the mas90 providex database. I will look for resources that would help me with it.
But I just created a login on sqlserver that matches with the username and password that I mentioned in the provider string of the linked server and I still ended up getting the error when I run the select query.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 30, 2011 at 12:55 pm
Really? I have linked servers set up with users that do not exist on the source server. Isn't the source simply passing whatever credentials you tell it to?
I didn't phrase that properly. I meant on the target server. It can't login there if the user doesn't exist there. You are absolutely correct in stating that it shouldn't be needed on the source server.
The reason I suggested that was in thinking that the target server wasn't handling the windows authentication properly. For the OP, a few questions:
1. Can you script out your linked server creation and post the TSQL here?
2. What versin of SQL Server is the source server?
3. Is your laptop a member of the domain or are you just a "guest" on the domain utilizing the internet connection?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 30, 2011 at 1:30 pm
1. Can you script out your linked server creation and post the TSQL here?
/****** Object: LinkedServer [MAS90_LINK] Script Date: 11/30/2011 15:26:01 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'MAS90_LINK', @srvproduct=N'MAS90', @provider=N'MSDASQL', @datasrc=N'SOTAMAS90', @provstr=N'DSN=SOTAMAS90; UID=barry; PWD=; Directory=\\operations\Apps\Sage\MAS90; Prefix=\\operations\Apps\Sage\MAS90\SY\, \\operations\Apps\Sage\MAS90\==\; ViewDLL=\\operations\Apps\Sage\MAS90\Home; Company=PHT; LogFile=\PVXODBC.LOG; CacheSize=4; DirtyReads=1; BurstMode=1; StripTrailingSpaces=1; SERVER=NotTheServer'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MAS90_LINK',@useself=N'False',@locallogin=NULL,@rmtuser=N'barry',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'MAS90_LINK', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MAS90_LINK', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MAS90_LINK', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MAS90_LINK', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MAS90_LINK', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MAS90_LINK', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MAS90_LINK', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MAS90_LINK', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MAS90_LINK', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MAS90_LINK', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MAS90_LINK', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MAS90_LINK', @optname=N'use remote collation', @optvalue=N'true'
GO
2. What versin of SQL Server is the source server?
@@version=Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Standard Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
3. Is your laptop a member of the domain or are you just a "guest" on the domain utilizing the internet connection?
Its a member of the domain (from the cmd i hit set and found I am in the domain)
Thanks for your inputs
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 30, 2011 at 1:33 pm
I gave you a wrong version for the sql server..please find the correct ones below
2. What versin of SQL Server is the source server?
Linked server is set up of sql server 2005 standard edition
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 30, 2011 at 1:36 pm
Sorry if I asked this already...but what's the version of your laptop SQL running? And are all 3 OS's 32-bit? I don't suppose your laptop is running 64-bit is it?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 30, 2011 at 1:43 pm
Sorry if I asked this already...but what's the version of your laptop SQL running? And are all 3 OS's 32-bit? I don't suppose your laptop is running 64-bit is it?
All the OS's are 32 bit. But the SQL Server I am using on my laptop is Microsoft SQL Server 2008 R2 SP2
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Standard Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 30, 2011 at 1:56 pm
Run this: SELECT name, value FROM sys.configurations
WHERE name = 'remote access';
Is the value 1?
Jared
Jared
CE - Microsoft
November 30, 2011 at 1:57 pm
In your linked server script you have @provstr=N'DSN=SOTAMAS90'
Is this a DSN that lives on the DB server?
And when you use your laptop, that DSN can't be found? Do you need the same DSN on your laptop?
November 30, 2011 at 2:05 pm
Randy Doub (11/30/2011)
In your linked server script you have @provstr=N'DSN=SOTAMAS90'Is this a DSN that lives on the DB server?
And when you use your laptop, that DSN can't be found? Do you need the same DSN on your laptop?
No, the DSN is used by the server not by the local client accessing it.
Jared
Jared
CE - Microsoft
Viewing 15 posts - 16 through 30 (of 67 total)
You must be logged in to reply to this topic. Login to reply