October 22, 2020 at 5:30 pm
All,
I have a DEV server where the SSISDB database ended up in emergency mode, and I couldn't do anything with it. The decision was made to not worry about recovery, so I deleted it from SSMS and then re-created the catalog using SSMS. This all worked, but then I was unable to deploy a project, with VS 2019 Pro saying that the client didn't hold a privilege needed. I looked at the server and found that the SSIS service was Stopped, and I tried to start it, but it's now getting a login failure for the NT Service\MsDtsServer150 account. I'm working with the infrastructure team on resolving that problem, but is that the cause of not being able to deploy?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 22, 2020 at 8:23 pm
I am pretty sure that if the SSIS service is not running, you can't do most SSIS related tasks such as deploying to an SSIS catalog.
I am a little surprised that it let you create the SSIS catalog without having SSIS running, but at the same time I think that is just a bunch of scripts that run to create the database and appropriate settings. I expect it doesn't need to talk to the SSIS service directly and I am pretty sure you can get some SSIS information (execution related information on an SSIS package for example) without having the SSIS service running.
My approach to correcting this would be to make an AD account to handle running that service and give it the appropriate permissions in the database. Offhand, I am not sure what permissions it needs, but google is your friend.
Failing that, I would check if NT Services\MsDtsServer150 account exists on the machine hosting SSIS and if it exists in the SQL Server. This is assuming that SSIS and SQL Server are BOTH running on the same physical machine or VM. If they are on different boxes, you will VERY likely need an AD account for the service.
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.
October 22, 2020 at 8:45 pm
I am pretty sure that if the SSIS service is not running, you can't do most SSIS related tasks such as deploying to an SSIS catalog.
I am a little surprised that it let you create the SSIS catalog without having SSIS running, but at the same time I think that is just a bunch of scripts that run to create the database and appropriate settings. I expect it doesn't need to talk to the SSIS service directly and I am pretty sure you can get some SSIS information (execution related information on an SSIS package for example) without having the SSIS service running.
My approach to correcting this would be to make an AD account to handle running that service and give it the appropriate permissions in the database. Offhand, I am not sure what permissions it needs, but google is your friend.
Failing that, I would check if NT Services\MsDtsServer150 account exists on the machine hosting SSIS and if it exists in the SQL Server. This is assuming that SSIS and SQL Server are BOTH running on the same physical machine or VM. If they are on different boxes, you will VERY likely need an AD account for the service.
The SSIS service actually does very little and it is not necessary for either package deployment or execution.
As far as I know, you should be able to deploy to the SSIS catalog if the following items are true:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 26, 2020 at 12:48 pm
As it turns out, I had tried messing around with the Service account, and via SQL Server Configuration Manager, and while that didn't help, a call to one of our infrastructure folks revealed a Group Policy conflict that was preventing the service account from having the permissions it needed. Once he straightened that out, the problem disappeared as if it had never existed. Sure was difficult to know what was going on, and fortunately, we have someone in infrastructure that knows AD and Group Policy well enough to be able to figure it out.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 26, 2020 at 1:41 pm
The service account for which service, exactly? Or is the same service account running multiple services?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply