Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

linked servers and account

Posted on 2014-04-14
6
Medium Priority
?
315 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 5

Assisted Solution

by:jayakrishnabh
jayakrishnabh earned 1000 total points
ID: 39998856
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 1000 total points
ID: 39998861
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
ID: 39998890
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 3

Author Comment

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

Expert Comment

by:Harish Varghese
ID: 39998918
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
ID: 40003637
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

596 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