I've just upgraded from SQL server 2014 to 2019 and also installed the latest version of SSMS.
My server has always been on a local machine (i.e. server name was (local) as far as I can remember, or if not it was just the name of the machine in Windows). I don't have external users, it's just a database I built for my own use.
When I try to connect to integration services in the latest version of SSMS to get jobs that I had previously built running, if I change server name from the default (which is the Windows machine name) to (local) or localhost, authentication method is greyed out as an option and stuck at Windows Authentication.
When I click connect, either leaving the server name as the Windows machine name with Windows Authentication, or changing it to (local) or local host (again with Windows Authentication because I don't have the option to change to SQL Server authentication), I get this error message. Can anyone tell me what I should do to connect to integration services?
The old version of SSMS and SQL 2014 are still on the machine, but unfortunately something during the upgrade process seems to have broken my connection in that to SSIS too. I can't connect there either and get a similar message.
TITLE: Connect to Server
------------------------------
Cannot connect to [WINDOWSMACHINENAME].
------------------------------
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
------------------------------
Connecting to the Integration Services service on the computer "SR2X5660" 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.
For help, click: http://go.microsoft.com/fwlink/?LinkId=506689
------------------------------
Connecting to the Integration Services service on the computer "[WINDOWSMACHINENAME]" 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.
------------------------------
BUTTONS:
OK
------------------------------
Did you follow the directions in the error message?
"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."
IE did you run SSMS as administrator (not your account, but right-click run as administrator)?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 15, 2020 at 7:21 pm
The error seems pretty clear - you need to be running SSMS as an administrator. With that said - why do you need to connect to SSIS directly?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
December 15, 2020 at 8:15 pm
Thanks for the help guys. For some reason I thought I was running as admin since I'm logged in to an administrator account in Windows, but of course it's required to right-click the executable and select 'run as administrator'. Doing that allowed me to connect to integration services in SSMS.
I connect to SSIS in order to allow jobs which are scheduled in SSMS to execute packages (which I previously built using Visual studio). What would the alternative to connecting to SSIS be?
December 15, 2020 at 8:45 pm
If you want automated processes - you can always schedule them in SQL Server agent, or windows task scheduler using dtsexec. But...since this is a personal workstation you can also just execute them in Visual Studio manually.
Since you are basically just running them manually anyways - then opening VS and executing there is just as effective as manually starting the way you are now starting them.
You can also setup the Integration Services Catalog - deploy your projects to the catalog and execute the packages from the catalog. The catalog is accessible when connected to the database engine, and it can be scripted - for example:
Declare @execution_id bigint;
Execute [SSISDB].[catalog].[create_execution]
@package_name = N'Package1.dtsx'
, @execution_id = @execution_id Output
, @folder_name = N'{ssisdb folder}'
, @project_name = N'{project name}'
, @use32bitruntime = False
, @reference_id = Null;
Select @execution_id;
Declare @var0 sql_variant = N'{Target Server}'
Execute [SSISDB].[catalog].[set_execution_parameter_value] @execution_id
, @object_type = 20
, @parameter_name = N'CM.Target.ServerName'
, @parameter_value = @var0;
Declare @var1 smallint = 2
Execute [SSISDB].[catalog].[set_execution_parameter_value] @execution_id
, @object_type = 50
, @parameter_name = N'LOGGING_LEVEL'
, @parameter_value = @var1;
Execute [SSISDB].[catalog].[start_execution] @execution_id;
Go
With the catalog - you can setup project parameters and modify them for execution - create project connections or package connections and set them to different values for execution as well as override package parameters and variables as needed.
All depends on your requirements...but connecting directly to integration services isn't needed anymore. The only time you really need to connect to that service is during setup - or if you need to modify specific settings for the service.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
December 15, 2020 at 9:01 pm
Thanks Jeffrey. I should have been more clear - when I said I use the packages in jobs I meant that I am running them in SQL Server Agent Jobs. They are imported first using a connection to SSIS in SSMS, into the MSDB location. I don't run them manually / open in Visual studio now that they've been imported there, they just run when scheduled in SQL server agent.
Great to learn about executing packages that are in the integration services catalogue using scripts, I could see that coming in very handy.
December 15, 2020 at 9:42 pm
I definitely recommend that you install and setup the Integration Services Catalog. It simplifies a whole lot around deploying and running packages. You don't need to connect to the SSIS service to deploy packages - they can be deployed directly from VS - in either package deployment model or project deployment model (catalog).
Switching over to the catalog - you can now change your VS project to use the project deployment model and deploy the whole project or just individual packages in the project (SQL Server 2016 and above). Using project deployment you now have the ability to create project parameters (variables that are scoped at the project level and available to every package) and project connections (a single connection definition that is available to every package in the project).
Once deployed - you can then access the Integration Services Catalog by connection SSMS to the database engine. You now have access to everything in one connection - plus the ability to script - as well as access to the execution reports.
Setting up the agent jobs is the same - you just reference the catalog instead of MSDB and set/configure the parameters/variables as needed for that job. All project parameters and all connections (either project or package) are available and can be overwritten as needed - plus the ability to add and maintain different environments and environment related variables.
Even in your current model - you don't need to login to SSIS to 'import' the packages. They can be deployed directly from VS to the selected server and location using Copy As.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
December 16, 2020 at 9:31 am
Wow that's very useful, thank you Jeffrey!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply