Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked server login problem Expand / Collapse
Author
Message
Posted Friday, February 21, 2014 3:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 139, Visits: 400
As part of security we have implemented groups and removed the individual logins from server and database.

but for linked servers access we are unable to add those groups. here if we are creating individual logins then only we can able to execute the linked server queries.

how can we add the groups to linked servers.we doesn't individual logins.

EXEC sp_addlinkedsrvlogin 'servername', 'false', 'domainusername', 'login', 'password'

i have executed the above query this is also creating the individual logins.

please help me out from this.
Post #1543891
Posted Monday, February 24, 2014 1:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 139, Visits: 400
can anyone please suggest on this
Post #1544391
Posted Monday, February 24, 2014 12:41 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 1,194, Visits: 2,213
Did you try the option " Be made using the login's security Context " ..

--
SQLBuddy
Post #1544684
Posted Tuesday, February 25, 2014 2:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 139, Visits: 400
Yes i have checked for all the options we are unable to add the groups name here.
Post #1544802
Posted Tuesday, February 25, 2014 12:34 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 1:26 PM
Points: 405, Visits: 1,431
Group? I assume it is windows security group in AD.

We use "Be made using the login's security Context" without any issue..... Or you may forget to create SPN?
Post #1545111
Posted Tuesday, February 25, 2014 12:50 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 1,194, Visits: 2,213
Logins will be in the AD group and you grant permissions to a role and add the group to that role. Then use " Be made using the login's security Context ".

--
SQLBuddy
Post #1545118
Posted Tuesday, February 25, 2014 1:55 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
Windows AD groups are not supported for linked Servers. This is by design and per MS, will not likely be changed going forward (based upon articles I've read in the past)

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1545131
Posted Wednesday, February 26, 2014 5:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 139, Visits: 400

Logins will be in the AD group and you grant permissions to a role and add the group to that role. Then use " Be made using the login's security Context ".

for the above which role we have to choose for Linked servers.is there any specific database roles are not?
Post #1545335
Posted Wednesday, February 26, 2014 7:35 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
For the same reason as what I mentioned above, it's a security risk. Linked servers allow security through a single account only: whether it be a sql account or a windows account.

I did a quick search and came up with the following explanation from Microsoft:
http://connect.microsoft.com/SQLServer/feedback/details/375983/allow-windows-groups-as-linked-server-logins


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1545394
Posted Wednesday, February 26, 2014 8:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 1,194, Visits: 2,213
There is no specific role for Linked Servers. Just you need to manage security at the SQL Server level and you can use DB roles for granting granular permissions.

--
SQLBuddy
Post #1545433
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse