| Author |
Topic |
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
 Posted - 2005-03-21 : 18:03:40
|
This issue has come up from time to time from our developers. They want to be able to run profiler and I am unwilling to give them sysadmin to do so. in the past, they were SOL, until I googled this:http://groups-beta.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/a881253ed627ea7c/bcc2abb72dc52f58#bcc2abb72dc52f58Brilliant!I created a new user, called it profiler_user, gave it a strong password and sysadmin role. I used a tool called Quick Batch File Compressor (http://www.abyssmedia.com/quickbfc/index.shtml) and the following command:@ECHO OFFprofiler /Sserver /Uuser /Ppassword /Tx Now the developer can run profiler against a specific instance without requiring an SA account.-ec |
|
|
jason
Posting Yak Master
164 Posts |
Posted - 2005-03-21 : 18:29:07
|
| Thanks for sharing eyechart! |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-21 : 18:54:58
|
| Wow, that is going to come in handy. I just recently granted temporary permissions to a developer to run SQL Profiler. He had the permissions for a couple of days in the development environment. Now I don't have to do this!I blogged this as this is very useful information for a DBA. Proper credit given.http://weblogs.sqlteam.com/tarad/archive/2005/03/21/4271.aspxTara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-03-21 : 22:35:12
|
| Run a network trace on the developers machine while you're doing this. See what it contains.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-03-22 : 14:27:19
|
quote:
Originally posted by derrickleggett
Run a network trace on the developers machine while you're doing this. See what it contains.
yeah, that is a good point. the connection is somewhat encrypted, but it is more like ROT13 than anything else. There are whitepapers available on how to crack SQL Authentication.To get around the weak encryption used for SQL authentication, you could use domain authentication using the CPAU tool from http://www.joeware.net/win/free/tools/cpau.htm instead. CPAU is just like runas, except you can provide a password instead of being prompted. The command line would look something like this:cpau -u domainname\username -p password -ex "profiler /E /Sinstance name /Tx" Put that in your batch file and compile it with the batch file compiler and you are done. You would also want to make sure that the domain user you created has access to write to the user's local drive. Otherwise you may have permission problems with the profiler trace output. Also, the CPAU utility needs to be in the their path for this to work.-ec |
|
|
|
rb1373
Yak Posting Veteran
93 Posts |
Posted - 2005-04-07 : 10:32:10
|
| Even though I specified our development SQL Server in the batch code, developers can still run traces against the production servers. I would prefer to have more control over production traces. Is there anyway to restrict what servers profiler can be executed against? Thanks,Ray |
|
|
|
jason
Posting Yak Master
164 Posts |
Posted - 2005-04-07 : 15:10:46
|
| Are the instances on one server? If no, you could configure a local admin for each server just for this purpose. |
|
|
|
edivar
Starting Member
1 Post |
Posted - 2005-06-23 : 09:59:29
|
Hi, was searching in the InterNet a problem that I am facing and looked at you speaking on this. It could explain me better where to create? how to create?Sorry but my English is not very good.ThanksEdivarquote:
Originally posted by eyechart
This issue has come up from time to time from our developers. They want to be able to run profiler and I am unwilling to give them sysadmin to do so. in the past, they were SOL, until I googled this:http://groups-beta.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/a881253ed627ea7c/bcc2abb72dc52f58#bcc2abb72dc52f58Brilliant!I created a new user, called it profiler_user, gave it a strong password and sysadmin role. I used a tool called Quick Batch File Compressor (http://www.abyssmedia.com/quickbfc/index.shtml) and the following command:@ECHO OFFprofiler /Sserver /Uuser /Ppassword /Tx Now the developer can run profiler against a specific instance without requiring an SA account.-ec
|
|
|
|
jpdejong
Starting Member
1 Post |
Posted - 2005-07-14 : 05:36:26
|
| Wow, is this the way you let people hack your system? In Profiler just click on Tools, Enterprise Manager and your developer / sysadmin can create his/her own SA account!I don't think I would implement this. I agree with the BIG problem of needing an sa account to profile the system. In my practice, I always create the trace, save it to a table/file and pass that to the developer. Always SAFE.JP |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-07-14 : 11:04:00
|
quote:
Originally posted by jpdejong
Wow, is this the way you let people hack your system? In Profiler just click on Tools, Enterprise Manager and your developer / sysadmin can create his/her own SA account!I don't think I would implement this. I agree with the BIG problem of needing an sa account to profile the system. In my practice, I always create the trace, save it to a table/file and pass that to the developer. Always SAFE.JP
It doesn't work that way. Sure, you can click on tools-->EM, but that won't run under the same user context that you started profiler under. Especially if you used sql authentication for the user as described in the first post. Did you even test this out, or are you just speculating? |
|
|
|
dmitiri
Starting Member
1 Post |
Posted - 2005-08-26 : 11:00:54
|
Does anyone have any insight as to why I get a "Failed to open a template file" error after running the said batch file?quote:
Originally posted by eyechart
This issue has come up from time to time from our developers. They want to be able to run profiler and I am unwilling to give them sysadmin to do so. in the past, they were SOL, until I googled this:http://groups-beta.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/a881253ed627ea7c/bcc2abb72dc52f58#bcc2abb72dc52f58Brilliant!I created a new user, called it profiler_user, gave it a strong password and sysadmin role. I used a tool called Quick Batch File Compressor (http://www.abyssmedia.com/quickbfc/index.shtml) and the following command:@ECHO OFFprofiler /Sserver /Uuser /Ppassword /Tx Now the developer can run profiler against a specific instance without requiring an SA account.-ec
|
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-08-26 : 11:39:01
|
quote:
Originally posted by dmitiri
Does anyone have any insight as to why I get a "Failed to open a template file" error after running the said batch file?
what happens if you run the profiler command straight from your command prompt? Do you still get an error?-ec |
|
|
|
thomadma
Starting Member
8 Posts |
Posted - 2006-05-15 : 11:28:11
|
| Hi, I keep getting the same error message "failed to open template" and tried to run it from the command prompt. Any ideas?Maria |
|
|
|
maxxxxel
Starting Member
1 Post |
Posted - 2006-05-18 : 04:48:22
|
| if you create a batch file that uses choice.exe that waits for a users input then look at your windows temp folder it creates a hidden copy of the decompiled bat file which shows the 'sa' password type %temp% in your start - run window. If you dont use a batch file that waits for user input then the temp file is deleted. There is still a chance that the file is not deleted if you close the batch file window with out using the command exit. Then the file batch file remains in the temp folder. This is too risky for me to have the programm create unencrypted temp files |
|
|
|
davidw
Starting Member
2 Posts |
Posted - 2006-06-28 : 08:47:44
|
Got a beta version off Abyssmedia, which does not create temp files  http://www.abyssmedia.com |
|
|
|
nyturn
Starting Member
1 Post |
Posted - 2008-10-03 : 09:30:00
|
quote:
Originally posted by maxxxxel
if you create a batch file that uses choice.exe that waits for a users input then look at your windows temp folder it creates a hidden copy of the decompiled bat file which shows the 'sa' password type %temp% in your start - run window. If you dont use a batch file that waits for user input then the temp file is deleted. There is still a chance that the file is not deleted if you close the batch file window with out using the command exit. Then the file batch file remains in the temp folder. This is too risky for me to have the programm create unencrypted temp files
I use ExeScript from http://www.scriptcode.com/ It converts batch vbs and other scripts to exe and encrypts file content to protect it from viewing and modification by other users. Also ExeScript lets execute your script right from the memory without unpacking it to some folder. |
|
|
|
sqldev2011
Starting Member
1 Post |
Posted - 2011-08-18 : 18:20:46
|
This is no longer a valid solution with the release of the new versions of Windows. Task Manger now shows the login information in the Command Line field Thanks,SQL Developerquote:
Originally posted by eyechart
This issue has come up from time to time from our developers. They want to be able to run profiler and I am unwilling to give them sysadmin to do so. in the past, they were SOL, until I googled this:http://groups-beta.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/a881253ed627ea7c/bcc2abb72dc52f58#bcc2abb72dc52f58Brilliant!I created a new user, called it profiler_user, gave it a strong password and sysadmin role. I used a tool called Quick Batch File Compressor (http://www.abyssmedia.com/quickbfc/index.shtml) and the following command:@ECHO OFFprofiler /Sserver /Uuser /Ppassword /Tx Now the developer can run profiler against a specific instance without requiring an SA account.-ec
|
|
|
|
|
HOWTO Run SQL Profiler without sysadmin rights