OpenDataSource with Security

[dk87]

Hi all!

A sister company of ours has a SQL server that I pull data from using one of our SQL servers. They do not want me to link to their server (I don't know why) so I use the following code within a stored procedure:

Select Field1, Field2, Field3
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=192.168.1.1;User ID=Username;Password=password'
         ).Instance.owner.Table
Where rundate = @vcRunDate

and this works fine. But I would rather not hard code the user name and password. I tried to create User Name and Password as parameters and pass them through, but it will not work. Is there a way that I can add this layer of security?

Thanks!

 

[dbomrrsm]

this seems to produce the same sql as you want

declare @user varchar (50),@Password varchar (50),@sql varchar (1000)
set @user = 'Fred'
Set @Password = 'verysecure'
set @sql ='Select Field1, Field2, Field3
FROM     OPENDATASOURCE('
         +''+'''SQLOLEDB'''+''+','+'
         ''Data Source=192.168.1.1;User ID='+@user+';Password='+@Password+
         '''+'').Instance.owner.Table
Where rundate = @vcRunDate'
print @sql

[blue]DBomrrsm[/blue]

 

[dk87]

Thanks, but now I get the following error:

Incorrect syntax near 'SQLOLEDB'

What I did was just cut my entire FROM statement out and replace it with the one supplied.

 

[dk87]

Any other ideas?

 

[dk87]

Got it!

CREATE procedure spTest as 

declare @user varchar (50),@Password varchar (50),@sql nvarchar (1000)
declare @Date datetime
set @Date = '12/22/2004'
set @user = 'User'
Set @Password = 'Password'


Set @sql = 'Select * FROM     OPENDATASOURCE(''SQLOLEDB'',
         ''Data Source=192.168.1.1;User ID='+@user+';Password='+@Password+
         ''').Instance.dbo.Table
Where SaleDate = @dDate
and MSCo = 40'

exec sp_executesql @sql, N'@dDate datetime', @Date