Remotely connecting to SQL Server 2008 Integration Services – Access is denied error.

Very annoying error and I haven’t seen clear concise instructions for getting this to work. I will attempt to do that here.

1. Open Administrative Tools > Component Services
2. Navigate to DCOM Config and open the properties for MsDtsServer100
3. Click on the Security tab
4. Add the appropriate users/groups that you need and assign their permissions to BOTH “Launch and Activation Permissions” AND “Access Permisions” (Both of whom, select Customize then Edit)
5. Click Ok to save changes and close those windows.
6. Right click on Computer, select Manage.
7. Navigate to System Tools > Local Users and Groups > Groups
8. Double click on Distributed COM Users
9. Add the users/groups you wanted to give permissions to.

Advertisement

A couple things to ALWAYS enable on your SQL Server

Trace flag 1222 – This little bugger will record deadlock information for you. A deadlock can occur when two transactions are trying to use the others’ resources and SQL Server can’t decide which transaction to run first. Enable it by either issuing the command DBCC TRACEON (1222,-1) or inside SQL Server Configuration Manager, select the SQL Server instance and add -T1222 to the startup parameters. 

Torn page detection – Torn page detection will allow you to see if any pages in your databases are corrupt. This is an indication of a physical hardware issue either on your hard drives or your disk controllers.

 

SQL Server – Restore Master Database

I have been working toward a SQL Server certification (70-432). This has resulted in me getting quite a good knowledge of how things work. Here is something interesting I have learned:

Steps (this only works if you have a good backup of Master)

1. Stop the SQL Server instance.

2. From a command prompt (must be running the command prompt as administrator), go into the Binn directory of the SQL Server instance and run “sqlservr -m”. This will start up a special single user mode.

3. Start another command prompt as administrator. Run sqlcmd and then perform the restore, something to the effect of: restore database master from backupdevice if you are using preprogrammed backup devices (something that is recommended).

4. Start SQL Server up normally.

5. Restore MSDB, then Model if necessary.

If you don’t have a valid backup, shame on you! But you can run setup again which will rebuild Master but you have to reattach all databases.