csalerno
asked on
SQL Server stored Proc debugging
I am having a nightmare in enabling sql server debugging on stored proc in my environment.
I have tried to use TOAD 5.8, VS 2010 and 2012 SQL Management Console without any success. I have a SQL Server Enterprise 2008 database that I am trying to remote debug SP's
I have made my client login account a member of the SysAdmin role using the sp_addsrvrolemember 'Domain\Name', 'sysadmin' I set the SQL Service to use the "Network Service" account instead of the "Local Service" account. I have enabled ports 135, 4500 & 500 thru our firewall.
What Am I missing?
I have tried to use TOAD 5.8, VS 2010 and 2012 SQL Management Console without any success. I have a SQL Server Enterprise 2008 database that I am trying to remote debug SP's
I have made my client login account a member of the SysAdmin role using the sp_addsrvrolemember 'Domain\Name', 'sysadmin' I set the SQL Service to use the "Network Service" account instead of the "Local Service" account. I have enabled ports 135, 4500 & 500 thru our firewall.
What Am I missing?
ASKER
In TOAD, getting the following:
Review the help file to ensure "<sql server name>" is configured for debugging.
Error message: Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)
In SQL MGT Console getting:
Unable to start the Transact-SQL debugger, could not connect to the Database Engine instance '<sql server name>'. Make sure you have enabled the debugging firewall exceptions and are using a login that is a member of the sysadmin fixed server role.
In running the sp_configure, what am I looking for?
Review the help file to ensure "<sql server name>" is configured for debugging.
Error message: Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)
In SQL MGT Console getting:
Unable to start the Transact-SQL debugger, could not connect to the Database Engine instance '<sql server name>'. Make sure you have enabled the debugging firewall exceptions and are using a login that is a member of the sysadmin fixed server role.
In running the sp_configure, what am I looking for?
ASKER
Also too, not using a Firewall or anything that would limit port usage inside my LAN
remote access, remote query timeout, remote proc trans
Also ensure that the debugger user has appropriate sysadmin access to the db server
Also ensure that the debugger user has appropriate sysadmin access to the db server
ASKER
Confirmed that the user has sysadmin access to the db server
access check cache bucket count 0 16384 0 0
access check cache quota 0 2147483647 0 0
Ad Hoc Distributed Queries 0 1 1 1
affinity I/O mask -2147483648 2147483647 0 0
affinity mask -2147483648 2147483647 0 0
Agent XPs 0 1 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
backup compression default 0 1 0 0
blocked process threshold (s) 0 86400 0 0
c2 audit mode 0 1 0 0
clr enabled 0 1 0 0
common criteria compliance enabled 0 1 0 0
cost threshold for parallelism 0 32767 5 5
cross db ownership chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
Database Mail XPs 0 1 0 0
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
default trace enabled 0 1 1 1
disallow results from triggers 0 1 0 0
EKM provider enabled 0 1 0 0
filestream access level 0 2 0 0
fill factor (%) 0 100 0 0
ft crawl bandwidth (max) 0 32767 100 100
ft crawl bandwidth (min) 0 32767 0 0
ft notify bandwidth (max) 0 32767 100 100
ft notify bandwidth (min) 0 32767 0 0
index create memory (KB) 704 2147483647 0 0
in-doubt xact resolution 0 2 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 64 0 0
max full-text crawl range 0 256 4 4
max server memory (MB) 16 2147483647 2147483647 2147483647
max text repl size (B) -1 2147483647 65536 65536
max worker threads 128 32767 0 0
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
Ole Automation Procedures 0 1 1 1
open objects 0 2147483647 0 0
optimize for ad hoc workloads 0 1 0 0
PH timeout (s) 1 3600 60 60
precompute rank 0 1 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
Replication XPs 0 1 0 0
scan for startup procs 0 1 0 0
server trigger recursion 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
SMO and DMO XPs 0 1 1 1
SQL Mail XPs 0 1 0 0
transform noise words 0 1 0 0
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
xp_cmdshell 0 1 0 0access check cache bucket count 0 16384 0 0
access check cache quota 0 2147483647 0 0
Ad Hoc Distributed Queries 0 1 1 1
affinity I/O mask -2147483648 2147483647 0 0
affinity mask -2147483648 2147483647 0 0
Agent XPs 0 1 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
backup compression default 0 1 0 0
blocked process threshold (s) 0 86400 0 0
c2 audit mode 0 1 0 0
clr enabled 0 1 0 0
common criteria compliance enabled 0 1 0 0
cost threshold for parallelism 0 32767 5 5
cross db ownership chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
Database Mail XPs 0 1 0 0
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
default trace enabled 0 1 1 1
disallow results from triggers 0 1 0 0
EKM provider enabled 0 1 0 0
filestream access level 0 2 0 0
fill factor (%) 0 100 0 0
ft crawl bandwidth (max) 0 32767 100 100
ft crawl bandwidth (min) 0 32767 0 0
ft notify bandwidth (max) 0 32767 100 100
ft notify bandwidth (min) 0 32767 0 0
index create memory (KB) 704 2147483647 0 0
in-doubt xact resolution 0 2 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 64 0 0
max full-text crawl range 0 256 4 4
max server memory (MB) 16 2147483647 2147483647 2147483647
max text repl size (B) -1 2147483647 65536 65536
max worker threads 128 32767 0 0
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
Ole Automation Procedures 0 1 1 1
open objects 0 2147483647 0 0
optimize for ad hoc workloads 0 1 0 0
PH timeout (s) 1 3600 60 60
precompute rank 0 1 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
Replication XPs 0 1 0 0
scan for startup procs 0 1 0 0
server trigger recursion 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
SMO and DMO XPs 0 1 1 1
SQL Mail XPs 0 1 0 0
transform noise words 0 1 0 0
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
xp_cmdshell 0 1 0 0ASKER
When I run the TOAD SQL Debugger Wizard I get this response:
Everything else in the log is successful but the below.
Client: sqldbreg2.exe path was not found.
Client: sqldbreg2.exe is not registered properly.
I cannot find any reference to sqldbreg2.exe.
Is it my problem and where can I find it?
Everything else in the log is successful but the below.
Client: sqldbreg2.exe path was not found.
Client: sqldbreg2.exe is not registered properly.
I cannot find any reference to sqldbreg2.exe.
Is it my problem and where can I find it?
Here is what you can try:
See if SQL Debugger feature is actually installed.
IF it is, http://technet.microsoft.com/en-us/library/cc646024.aspx consists of steps to follow for setting up firewall rules
See if SQL Debugger feature is actually installed.
IF it is, http://technet.microsoft.com/en-us/library/cc646024.aspx consists of steps to follow for setting up firewall rules
ASKER
Again, this suggestion is centered around Firewall allowances and I have the Firewalls turned off.
How can I confirm that the SQL Debug support is installed on the server?
How can I confirm that the SQL Debug support is installed on the server?
Can you login to the server, run SSMS locally, and try debug?
ASKER
When I try to run from the SSMS on my server, debug is not even available?
Does that mean that the debug support is not installed on the server?
The below is from the About screen of the SSMS.
Microsoft SQL Server Management Studio 10.0.5500.0
Microsoft Analysis Services Client Tools 10.0.5500.0
Microsoft Data Access Components (MDAC) 3.86.3959
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.3649
Operating System 5.2.3790
Does that mean that the debug support is not installed on the server?
The below is from the About screen of the SSMS.
Microsoft SQL Server Management Studio 10.0.5500.0
Microsoft Analysis Services Client Tools 10.0.5500.0
Microsoft Data Access Components (MDAC) 3.86.3959
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.3649
Operating System 5.2.3790
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
changed the login that the sql server used on the SQL Service
Did you check
Open in new window