Solved

linked servers and account

Posted on 2014-04-14
6
278 Views
Last Modified: 2014-04-16
I am trying to do some risk assessment work to determine any risks posed by current linked servers. I have the output of sys.linked_logins, sys.servers  and sys.server_principals which seem to be the main tables containig information about the links. But can anyone give any pointers on determining under which account our instance is connecting to the remote instance, and vice versa. Somewhere there must be clues as to which account is used in the link?
0
Comment
Question by:pma111
  • 3
  • 2
6 Comments
 
LVL 5

Assisted Solution

by:jayakrishnabh
jayakrishnabh earned 250 total points
Comment Utility
Accounts used for the linked server can be found from the linkedserver properties window or create script of that
0
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 250 total points
Comment Utility
There is a script available at below link that lists all linked servers on current server and the logins associated with them. Please check:
http://gallery.technet.microsoft.com/scriptcenter/Get-List-of-Linked-Server-d6c95d9c

-Harish
0
 
LVL 3

Author Comment

by:pma111
Comment Utility
Cant I use the information in the tables I already have as mentioned above?

Just seen the SQL so looks like I can use the data I already have as its using those tables in the script you linked to.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 3

Author Comment

by:pma111
Comment Utility
so remote_name indicates the account in which you are connecting to the remote instance?
0
 
LVL 12

Expert Comment

by:Harish Varghese
Comment Utility
Right. And Local Login "Uses Self Credentials" indicates that the same credentials as the logged in user will be used to connect to the remote server.
0
 
LVL 12

Expert Comment

by:Harish Varghese
Comment Utility
Here is the SQL (for future reference):
SELECT ss.server_id 
          ,ss.name 
          ,'Server ' = Case ss.Server_id 
                            when 0 then 'Current Server' 
                            else 'Remote Server' 
                            end 
          ,ss.product 
          ,ss.provider 
          ,ss.catalog 
          ,'Local Login ' = case sl.uses_self_credential 
                            when 1 then 'Uses Self Credentials' 
                            else ssp.name 
                            end 
           ,'Remote Login Name' = sl.remote_name 
           ,'RPC Out Enabled'    = case ss.is_rpc_out_enabled 
                                   when 1 then 'True' 
                                   else 'False' 
                                   end 
           ,'Data Access Enabled' = case ss.is_data_access_enabled 
                                    when 1 then 'True' 
                                    else 'False' 
                                    end 
           ,ss.modify_date 
      FROM sys.Servers ss 
 LEFT JOIN sys.linked_logins sl 
        ON ss.server_id = sl.server_id 
 LEFT JOIN sys.server_principals ssp 
        ON ssp.principal_id = sl.local_principal_id

Open in new window

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now