Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
security consequences when using openrowset

Unanswered security consequences when using openrowset

  • Monday, January 14, 2013 7:10 AM
     
     
    Sign In to Vote
    0

    Hi eb

    I want the easiest way to calling sp_help_job inside stored procedure

    (without having to insert results into temporary table or call xp_sqlagent_enum_jobs).

    So I wrote this:

    select * from openrowset ('SQLNCLI','DRIVER={SQL Server};SERVER=mySrvr;UID=myUsr;PWD=myPwd','EXEC msdb.dbo.sp_help_job').

    As you can see I am not using a linked server but a direct connection to target server.

    I am not comfortable however with using explicit user name and paswword inside code

    allthough i cant think of scenario where this can be abused from outside my stored procedure.

    Can you??

    TIA

    Rea

     

All Replies

  • Monday, January 14, 2013 7:51 AM
     
     
    Sign In to Vote
    0

    If you need to prevent any person reviewing code of the procedure  seeing this credentials, you may store user name and password in some safe (restrictred access) place within the DB separatley from the procedure. If you're concerned of other threats tell us more about the application context.

    Serg

     
  • Monday, January 14, 2013 7:53 AM
     
     
    Sign In to Vote
    0
    Take a look at check execution_status in sp_help_job..
     
  • Monday, January 14, 2013 8:15 AM
    Answerer
     
     
    Sign In to Vote
    0

    Why do you need OPENROWSET? Is that possible to create a linded server and call this sp ? Is that possible to specify Windows Authentication in the connection string?

    select * from openrowset 
    ('SQLOLEDB', 'Server=(local);Trusted_Connection=yes;Encrypt=yes;', 'select 
    *, @@servername from sysprocesses')
    -- I added 'Encrypt=yes;' for no particular reason.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

     
  • Monday, January 14, 2013 9:52 AM
     
     
    Sign In to Vote
    0

    Thanks

    can't use .xp_sqlagent_enum_jobs  due to permissions restrictions...

     
  • Monday, January 14, 2013 9:56 AM
     
     
    Sign In to Vote
    0

    thanks

    not possible to create linked server or use windows authentication due to permissions restrictions

    and consolidation environment configurations.

    .xp_sqlagent_enum_jobs  is also out of limits...

    My question there for is can you see security issues regarding the way i am using openrowset with sql server authentication

    where user name and password are exposed in stored procedure code?

    Thanks!

    Rea

     
  • Monday, January 14, 2013 10:06 AM
    Answerer
     
     
    Sign In to Vote
    0
    Everyone who can open the stored procedure will see the user and password you specify in connection string.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

     
  • Monday, January 14, 2013 11:18 AM
     
     
    Sign In to Vote
    0

    This is not a security threat as only development team has direct access to code.

    From an external world point of view - no threats in this case??

    Thanks :)

     
  • Monday, January 14, 2013 11:41 AM
    Answerer
     
     
    Sign In to Vote
    1
    If it is not web server then I see nothing not using it in that case.....

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance