April 15, 2021 at 4:29 pm
We have a dev that used to use SSMS in order to debug code that was related to SQL. I guess MS has taken that piece out of SSMS and now they are supposed to do this with Visual Studio. Problem is, now we can't seem to get that to work right.
Each time they try with VS19 to a SQL 2019, the following error comes up:
Unable to start the Transact-SQL debugger, could not connect to the Database Engine instance '<instance 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. The RPC server is unavailable."
The firewall is disabled and according to what I have read, the debugging person's account needs to be in the SQL Server's "sysadmin" role. Done that and also made that user the db owner for good measure. FYI, I've tried using domain group and adding them to the group and also just using their individual domain accounts.
Everything I try reaps the same error message.
Please advise.
April 15, 2021 at 7:01 pm
With debugging SQL Server code, I do not use the debugger. I've never found it to be that useful with respect to TSQL. Maybe my code is not interesting enough for the debugger, but I generally just put in PRINT statements or SELECT statements (with a --DEBUG line before and after so I am 100% certain what code to remove before go live).
I've never had luck getting the debugger to work right for what I need either.
On top of that, if someone isn't supposed to be a full administrator on the system, they do NOT get sysadmin permissions no matter what. sysadmin lets them do literally anything on the SQL Instance (not just the database). They want to change the backups to not run? they can do that. Drop a database? they can do that. Grant others sysadmin access? they can do that. On a test/dev system I'd be less concerned, but it is still dangerous to just "hand out" sysadmin permissions because someone asked for it.
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.
April 15, 2021 at 7:08 pm
Your concerns are noted and also share them. However this is a test environment so not overly concerned. From my searching around, that seems to be the only way to get debugging to work unfortunately. Unless someone knows of a another way. But in my case, I can't even get the prescribed method to even work. 🙁
Thanks.
April 15, 2021 at 9:05 pm
My next step then would be to check the error that popped up.
The error message is:
Unable to start the Transact-SQL debugger, could not connect to the Database Engine instance '<instance 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. The RPC server is unavailable."
So lets break that down into the 2 issues it lists. Is the login a member of the sysadmin fixed server role? You indicated it is, so that is likely not the problem. The second error is likely the problem. Is the RPC server available? Here, you want to make sure that the RPC related services are enabled on the server machine (you MAY need them on on the client machine as well), plus you'll need to open up those firewall ports on the server, the client, and any hardware firewall you may have in place.
My "other way" for debugging SQL stuff is to take the stored proc apart and run it manually, piece by piece with PRINT statements or SELECT statements in there to help with debugging. This does not require sysadmin permissions, just view definition on the object I am wanting to look at.
Just my 2 cents though.
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.
April 21, 2021 at 1:59 pm
Thanks Brian for the suggestions
I don't know the details what the programmers are doing but when I suggested the Print and Select option, they are aware of that but said that that is too simplistic for what they are doing.
So for what ever reason, I had them try again and today it worked! I didn't make any additional changes. So the only thing that comes to mind was that maybe some log out and log in process needed to occur after the permissions were set?
Anyway, thanks again!
April 21, 2021 at 4:18 pm
Having then log out and back in would probably make sense as I expect it would refresh their permissions.
As for what they are doing in SQL, I would be mildly concerned about that chunk of code if using PRINT/SELECT statements is "too simplistic for what they are doing". My concern would be that they are writing TSQL like they would C#. In C#, for example, if you had a table of data and you wanted to multiply the "sales price" by the "exchange rate" when the currency was not equal to the functional currency (as an example), you would do it with a loop. Which loop methodology you pick would be more of a personal preference.
Now, doing the same thing in SQL would be a cardinal sin to use a loop and your performance would be horrid, especially on large tables.
I would be curious what they are making SQL do and if it makes sense to make SQL do what they want it to. My opinion, once your SQL gets too complex for a simple PRINT or SELECT statement to be helpful in debugging, you are going to have trouble testing that SQL code. If you can't properly test the SQL code, are you comfortable with it going live? What I mean by testing is given valid input, do you get valid output? Given an edge case, do you get valid output? Given invalid input, do you get valid output? These things are EASY to test when your SQL code is simple (similar to a C# function is easy to test when it is simple), but can become challenging, and sometimes impossible, when the code gets too complex.
Now, on the other hand, if the SQL code is so complex that PRINT and SELECT are not valid methods for debugging it, is the code maintainable? What I mean here is, in 2 years when maintenance needs to be done on the stored procedure (I hope it is a stored procedure anyways) due to performance issues (or bugs due to changing company requirements), is anyone going to be able to go in and change the code to work more efficiently or fix the bugs? OR is the code so complex that at this point it is a "duct tape and super glue" type stored procedure where it works and nobody better touch it because it will break if you stare too long at it?
MAYBE SQL is the right tool for this process, but it might not hurt to look at what the code is doing and offer advice on how to improve the code. I find that long, complex queries can be problematic in terms of concurrency. What I mean is when that code goes into test, it may work GREAT... while a single user uses it at a time. But once it hits production, you may notice a lot of blocking or worse - deadlocks. If you are the DBA, those things are likely going to fall on you to investigate and solve. And if they (or you) go with (or have already gone with) the NOLOCK "fix" for blocking, you are going to regret it.
I would advice (presuming time permits it) that you and the developers review the complex code and see what makes sense to do in SQL and what makes sense to do outside SQL and (if possible) what can be optimized. As I said above, loops in SQL are strongly advised against and I have found that in MOST (not all) cases, they are not required.
The TL;DR version of the above - I would take a look at the code they want to push to your databases. You are the DBA and if they say "it worked great on test, why is it so slow on live?", you are the one who will need to investigate it and give recommendations and if you tell them "it is slow because your SQL code is written like a C# developer", it won't go over well I'm sure. BUT if you tackle that discussion PRIOR to it going live and give them tips on how to make their SQL more efficient and easier to maintain without needing a debugger, they will eventually write better code and you will have less worry and concern; especially about giving someone who isn't a DBA sysadmin on your system...
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply