xp_cmdshell and osql and visible passwords

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

xp_cmdshell and osql and visible passwords

Author

Topic

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-06-21 : 09:17:03

I am trying to get around the problem of not being able to use strings longer than 8000 characters with dynamic sql. To do this I have written a stored procedure that uses xp_cmdshell to bcp a table containing the sql into a text file. I then use xp_cmdshell to execute the osql utility.

The problem is that I have to pass the username and password to the sp (and subsequently to the osql utility). As the sp is executed via a web front end I do not want to explicitly pass the username and more importantly the password.

Anyone any ideas on how I can achieve this?

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-21 : 09:41:16

I'm not sure I fully grasp what you are doing, but I can submit that if you run osql from xp_cmdshell and don't set the flags for username and password, it will assume the domain credentials . . .


master..xp_cmdshell 'osql /Q "select nt_username from master..sysprocesses where spid = @@spid"'

...will tell you the Windows users who's credentials are being used to execute the cmdshell command....

<O>

colinm
Yak Posting Veteran

62 Posts

Posted - 2002-06-21 : 09:52:46

You can use a trusted connection with BCP. This will mean no password encoded, but obviously you'll have to set up a SQL login for it.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-21 : 10:26:03

quote:

You can use a trusted connection with BCP. This will mean no password encoded, but obviously you'll have to set up a SQL login for it.

Semantics, I know, but to avoid confusion, for a trusted connection you would set up a domain login, not a SQL login.

setBasedIsTheTruepath
<O>

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-06-21 : 11:49:07

David, this seems a excessive workaround to the character limitation.

Have you tried combining the strings:
DECLARE @SQL1 as nvarchar(4000)
DECLARE @SQL2 as nvarchar(4000)
DECLARE @SQL3 as nvarchar(4000)
DECLARE @SQL4 as nvarchar(4000)

SELECT @SQL1 = 'SELECT * FROM sysobjects '
SELECT @SQL2 = 'ORDER BY name '
SELECT @SQL3 = 'SELECT count(*) AS CountAll FROM sysusers '
SELECT @SQL4 = 'SELECT count(*) AS CountC FROM syscolumns'

EXEC (@SQL1 + @SQL2 + @SQL3 + @SQL4)

Or, perhaps creating UDFs that contain the bulk of the code and would allow you to stay within the 8000 characters? Should also give you better performance.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-21 : 11:58:19

I think you gotta look at how your going about this one david. Breaking the @var into
@var1 = 'update tablename set'
@var2 = 'column = column............'
@var3 = 'morecolumn - morecolumn.....'
@var4 = 'where .....'
@var5 = 'having.... '

execute (@var1 + @var2 + @var3 + @var4 + @var5)

how bout give us the dynamic sql your trying to send and we can help there.

Other option is to pass variables into a stored proc... just pass along the @vars that you want to the stored proc and let the stored proc run something like
execute ('update table set ' +@collist1+@collist2+' where ' +@where statment)

sniped... By a innocent little yellowbug.. heh. Glad to know I atleast think along the same lines as someone that knows what they are doing.... sometimes.
-----------------------
Take my advice, I dare ya

Edited by - M.e. on 06/21/2002 11:59:54

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-06-24 : 05:08:59

Thanks as ever to speedy responses!

As for the alternatives..

I knew about the possibility of using exec @var1+@var2 etc. Unfortunately this does not help in my particular application. It would if there was a way of dynamically creating the variable names (kind of like an array). This is because the dynamic SQL statement I am trying to create has a variable number of parts, dependant on data that is held in "metadata" tables.

The solution of inserting the statements in a table would just give far more flexibility.

As for the answers regarding log-ins etc I am not particularly up on how trusted connections, domain log-ins, sql server log-ins all work. The web front end has been written by a third party so I am not sure of the mechanics of how it works. My assumption is that there is a connection string on the web page and that the SQL statements (stored procedure calls) are then passed to the SQL Server. The communications are all fully encrypted so it may be that passing the password is not an issue anyway.

I guess my question comes down to whether a trusted connection can be used if the SQL connection string has already specified the user and password (so long as there is an equivalent Windows log-in on the SQL Server box)?

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources