SQL Server 2012 Integration Services failed when connecting thru SSMS

sql-server ssis
Raymund picture Raymund · Dec 18, 2012 · Viewed 75.2k times · Source

I had recently installed SQL server 2012 and I used mostly the default settings. Database works fine and I can happily connect using SSMS (SQL Server Management Studio) but when I connect to the Integration Services Server I get this message

Connecting to the Integration Services service on the computer "localhost" failed with the following error: "Access is denied."

By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.

here is the screenshot enter image description here

I am not sure why but I am the domain admin and have full rights over the server. Also why when I connect from my Desktop it can successfully connect, only if I connect from the server itself which gives me this issues. How do I fix this so that I can make SSMS on the server connect to its Integration Services instance.

Answer

billinkc picture billinkc · Dec 18, 2012

As I understand it, User Access Control, or UAC, can basically intercept requests for your group membership so in this case, it appears it was preventing your membership getting passed to SQL Server.

Others have noted in their comments that you may still need to right click and run SSMS as an Administrator.

As noted by an astute observer "This is a quick-fix, not a real solution. People shouldn't just be running stuff as administrator. These security walls are in place for a reason" And I agree. UAC is designed to get Windows users into a Principle of least privilege mindset - only escalate to a powerful account when required. The issue is that SSMS is known to not "play well" with UAC. As I see it, this leaves you with three options

  • You can turn off UAC and get your work done
  • Leave UAC on and tell your boss you are unable to work
  • Write your own query tool that is not affected by UAC

Related questions

'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)

I get this error when I try to connect to any excel through MSSQL Server Data Import i.e. SSIS package The Microsoft.ACE.OLEDB.16.0 provider is not registered on the local machine. (System.Data) It is not the same …

Read More
Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot

I'm trying to import a flat file into an oledb target sql server database. here's the field that's giving me trouble: here are the properties of that flat file connection, specifically the field: here's the error message: [Source - 18942979103_txt [424]] …

Read More
How do I fix the multiple-step OLE DB operation errors in SSIS?

I'm attempting to make a DTS package to transfer data between two databases on the same server and I'm getting the following errors. Iv read that the Multiple-step OLE DB operation generated error can occur when you are transferring between …

Read More