August 15, 2008 at 1:16 pm
What kind of permissions do a user need to have to connect to a linked server?
I have a linked server L1, and a user U1. If U1 is given sysadmin permissions, it connects to linked server fine. If I remove sysadmin permissions from U1, the connection fails with error:
"Error: 7416 Access to remote server is denied because no login mapping exists."
I did not test with any other permission other than sysadmin. User U1 has dbcreator server role assigned to it and datareader, datawriter and ddladmin database roles assigned to it.
The user login U1 is used to run ETL process, thus needs to connect to remote server to get source data but does not need to perform other sysadmin functions.
August 15, 2008 at 1:31 pm
KB (8/15/2008)
What kind of permissions do a user need to have to connect to a linked server?
Virtually none, which is, IMHO sometimes a problem.
What's wrong is that you haven't set up anything that says how logins on the one server map to logins on the remote server. Go to the linked server, right click, get the properties and go to the security tab.
Your options are either to map current credentials, which means that a person needs to have a matching login on the remote server, or to map logins on the local to the remote.
Be careful. It's very easy to map logins so that someone with no rights at all on the local server has sysadmin on the remote. If you choose to map the logins and specify passwords, be careful what permissions the mapped login has.
Edit: In addition, if the ETL process is fetching a lot of rows, may I suggest using DTS or SSIS instead. Large amounts of rows via a linked server has quite an impact on TempDB and can be slower than a proper ETL tool
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2008 at 1:46 pm
I did add login U1 in security tab under the 'Local Server login to remote server login mappings' section and provided the remote user and remote password there. That did not work.
So I also tried removing it from the list, and choosing option 'Be made using this security context' option and providing remote user and passowrd there. That did not work either.
Both these options work if I make U1 a sysadmin, so I thought maybe something to do with permissions.
August 15, 2008 at 2:06 pm
are you connecting to another 2005 instance?
I've had better luck with the commands vs the gui.
EXECUTE sp_addlinkedserver @server='remoteserver', @srvproduct='SQL Server'
EXECUTE sp_addlinkedsrvlogin @rmtsrvname='remoteserver', @useself='false',
@rmtuser='username', @rmtpassword='password'
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
August 15, 2008 at 2:31 pm
I am connecting to another SQL Server 2000 instance.
I was using 'Other data source' and 'SQL Native Client' to configure linked server. After looking at Jason's example I changed it to SQL Server and now it works! Thanks Jason!!
The weird thing is that initial linked server using 'SQL Native Client' was successfully connecting when user U1 had sysadmin permissions on local server but gave connection errors if I removed that permission. Both times I used the same remote user.
August 15, 2008 at 2:42 pm
One other gotcha if you ever have a 64 bit instance of 2005 trying to connect to a linked server that's a 32 bit instance of SQL 2000 then you can execute the following in the master on the 2000 instance. I had to do this recently:
one source (http://joelmansford.wordpress.com/2007/08/10/connecting-sql-server-2005-x64-to-sql-server-7-32bit-as-a-linked-server/[/url])
create procedure sp_tables_info_rowset_64
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
as
declare @Result int set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
August 15, 2008 at 4:19 pm
Thanks for the information Jason!
For now I was doing this on my Development server which is a 32 bit 2005 connecting to 32 bit 2000. But when I move these changes to Production I might run into these issues because Production is a 64 bit 2005 and would connect to the same 32 bit 2000, you saved me a lot of troubleshooting 🙂
March 30, 2009 at 9:02 am
Jason Crider (8/15/2008)
One other gotcha if you ever have a 64 bit instance of 2005 trying to connect to a linked server that's a 32 bit instance of SQL 2000 then you can execute the following in the master on the 2000 instance. I had to do this recently:one source (http://joelmansford.wordpress.com/2007/08/10/connecting-sql-server-2005-x64-to-sql-server-7-32bit-as-a-linked-server/[/url])
create procedure sp_tables_info_rowset_64
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
as
declare @Result int set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
Thanks Jason 🙂
You have really saved my day and Job. I created that procedure and the Link started working from SQL2005 64x to SQL2000 32x.
Thanks again!
Anukul
[font="Verdana"]
-------------------------------------
Anukul
My Blog
My Twitter Profile
My Stumbles[/url]
[/font]
March 30, 2009 at 9:46 am
Don't forget about the microsoft link that discusses their fix:
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
I believe that the best method is that one, and if it doesn't work then you try the wrapper.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply