| By product |
| Other |
|
Troubleshooting Troubleshooting and known issues with ApexSQL Tools Debugging remote SQL Server stored procedures and user defined functions Applies to ApexSQL Edit Symptoms Debugging of local stored procedure/UDF from ApexSQL Edit works fine, but when attempting to debug stored procedure/UDF on remote SQL Server, it fails with error message or even “CoUnmarshal” exception. Cause Debugging is the process of locating and fixing errors and “bottle-necks” in stored procedures and user defined functions. Debugging of remote SQL Server objects is complicated as it requires special configuration of the remote machine. Resolution Following steps must be performed/verified in order to set up remote debugging. These steps must be applied for both SQL Server Management Studio add-in (ApexSQL Debug) and for the debugger integrated into ApexSQL Edit. 1. Verify that both local and remote machines are in the same workgroup/domain 2. Firewall on machine with SQL Server installed (remote machine) must be configured properly: - All: TCP/IP port 135, UDP 4500 and UDP 500 ports must be opened. In case this is not done you can get a “CoUnmarshal” exception - Make sure sqlservr.exe is added to your firewall exception list 3. Configure Local Security Settings on the remote machine (you can find that under the Administrative Tools in the Control Panel). Go to Local Policies Security Options Network Access: Sharing and Security model for local accounts and set it to "Classic – local users authenticate as themselves"  In case that the remote machine is domain controller, Security Options are configured as follows: a) Log on using an administrator account b) Open the Active Directory Users and Computers tool c) Right-click the container holding the domain controller and click Properties d) Click the Group Policy tab, and then click Edit to edit the Default Domain Policy e) In the Group Policy window, expand Computer Configuration, navigate to Windows Settings, to Security Settings and then to Local Policies f) Select Security Options  g) To configure a Security Options policy, double-click a policy option or right-click on it and select Security. This opens a Security Policy Setting dialog box   4. Your SQL Server service needs to be set to be logged on as an appropriate user (remote). To configure a SQL Server service, open SQL Server Configuration Manager Tool. Locate SQL Server 2005 Services, double-click desired SQL Server instance service. This opens a SQL Server properties dialog box Instead of a Built-in account choose a particular user account (user must be a member of local administrators on both local and remote machine) and apply changes.   Once the setup is completed, you should be able to connect to a remote SQL server and perform debugging from SQL Server Management Studio:    Debugging from ApexSQL Edit:    Known limitations: 1) If you are using Built-in accounts, you will not be able to perform remote debugging and you’ll probably get a "Not enough access rights" error message 2) Same user (with exact password) must be: - a member of the local administrators group on a computer running SQL Server 2005/2008 and each computer which will be used for remote debug - used for starting SQL Server service (as noted above) 3) Same SQL Server services setting must be applied for each SQL Server instance you wish to debug (in case that you have several SQL Server 2005/2008 instances) About ApexSQL Edit ApexSQL Edit is a SQL Server code editor with advanced SQL formatting, refactoring and autocomplete functions. Edit SQL using code snippets and templates in a single integrated environment. Last updated 25 November 2011 Labels: ApexSQL Debug, ApexSQL Edit ... |
