SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 HOWTO Run SQL Profiler without sysadmin rights
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 03/21/2005 :  18:03:40  Show Profile  Reply with Quote
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#bcc2abb72dc52f58

Brilliant!

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 OFF
profiler /Sserver /Uuser /Ppassword /Tx


Now the developer can run profiler against a specific instance without requiring an SA account.



-ec




Edited by - eyechart on 03/21/2005 18:35:04

jason
Posting Yak Master

164 Posts

Posted - 03/21/2005 :  18:29:07  Show Profile  Reply with Quote
Thanks for sharing eyechart!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 03/21/2005 :  18:54:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
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.aspx

Tara

Edited by - tkizer on 03/21/2005 19:34:07
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 03/21/2005 :  22:35:12  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
Run a network trace on the developers machine while you're doing this. See what it contains.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 03/22/2005 :  14:27:19  Show Profile  Reply with Quote
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

Edited by - eyechart on 03/22/2005 14:30:26
Go to Top of Page

rb1373
Yak Posting Veteran

USA
93 Posts

Posted - 04/07/2005 :  10:32:10  Show Profile  Visit rb1373's Homepage  Reply with Quote
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
Go to Top of Page

jason
Posting Yak Master

164 Posts

Posted - 04/07/2005 :  15:10:46  Show Profile  Reply with Quote
Are the instances on one server? If no, you could configure a local admin for each server just for this purpose.
Go to Top of Page

edivar
Starting Member

Brazil
1 Posts

Posted - 06/23/2005 :  09:59:29  Show Profile  Reply with Quote
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.

Thanks

Edivar



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#bcc2abb72dc52f58

Brilliant!

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 OFF
profiler /Sserver /Uuser /Ppassword /Tx


Now the developer can run profiler against a specific instance without requiring an SA account.



-ec





Go to Top of Page

jpdejong
Starting Member

Netherlands
1 Posts

Posted - 07/14/2005 :  05:36:26  Show Profile  Reply with Quote
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
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/14/2005 :  11:04:00  Show Profile  Reply with Quote
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?

Go to Top of Page

dmitiri
Starting Member

1 Posts

Posted - 08/26/2005 :  11:00:54  Show Profile  Reply with Quote
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#bcc2abb72dc52f58

Brilliant!

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 OFF
profiler /Sserver /Uuser /Ppassword /Tx


Now the developer can run profiler against a specific instance without requiring an SA account.



-ec





Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/26/2005 :  11:39:01  Show Profile  Reply with Quote
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
Go to Top of Page

thomadma
Starting Member

8 Posts

Posted - 05/15/2006 :  11:28:11  Show Profile  Reply with Quote
Hi,

I keep getting the same error message "failed to open template" and tried to run it from the command prompt. Any ideas?

Maria
Go to Top of Page

maxxxxel
Starting Member

1 Posts

Posted - 05/18/2006 :  04:48:22  Show Profile  Reply with Quote
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

Edited by - maxxxxel on 05/18/2006 06:04:32
Go to Top of Page

davidw
Starting Member

2 Posts

Posted - 06/28/2006 :  08:47:44  Show Profile  Reply with Quote
Got a beta version off Abyssmedia, which does not create temp files

http://www.abyssmedia.com
Go to Top of Page

nyturn
Starting Member

1 Posts

Posted - 10/03/2008 :  09:30:00  Show Profile  Reply with Quote
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.
Go to Top of Page

sqldev2011
Starting Member

1 Posts

Posted - 08/18/2011 :  18:20:46  Show Profile  Reply with Quote
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 Developer

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#bcc2abb72dc52f58

Brilliant!

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 OFF
profiler /Sserver /Uuser /Ppassword /Tx


Now the developer can run profiler against a specific instance without requiring an SA account.



-ec





Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000