Issue – Today I faced very weird issue; on multi instance SQL Server machine, couple of SQL Services was not starting after Server reboot ( In my case it was SQL 2014 and SQL 2016 ) and every time I was trying to restart my SQL Services, I was getting below error message –
Solutions – On error message it’s look like some other processes was trying to use SQL default port 1433.
Hence it was not allowing to restart SQL Services with Port 1433.
On my server, we have multiple SQL Services installed ( SQL 2008 to SQL vNext ) hence I started checking if any of these SQL Server have assigned static port 1433.
I start checking assigned port for all SQL Server and I found SQL Server 2014 and SQL Server 2016 was having assigned static port 1433.
In order to resolve the issue, I removed static port entry and restart my SQL Services and it started successfully.
My Development team contacted me and said they are not able to access database “The server principal “XYZ\USER” is not able to access the database “YourDBName” under the current security context”
when I looked into server server I found –
- Login was there and access was granted.
- When I looked into database I found user was disable ( Red down arrow mark )
Here are two solutions –
- Most of the people recommended to recreate use by deleting and reading into database.
But main reason why it’s disabled because user is not having connect permission to database.
- if you grant connect permission it will resolve your issue.
grant connect to [xyz\user]
SSIS access permissions are managed via Component Service under DCOM Config
1. Run –> dcomcnfg.exe or Component Services -> Computers -> My Computer -> DCOM Config -> MSDTSServer
2.Right click on MSDTSServer or MSDTSServer100 ( based on SQL version )
4.Click on the Security tab
5.Select Customize and add the users/groups to the Launch/Activation and Access tabs accordingly
6.Add the user to the local Distributed COM Users group
7.Restart the SSIS
How to add user in local Distributed COM Users group
- Right-click My Computer>Manage>Configuration>Local Users & Groups
- Look for the Distributed COM Users group and add the account.