August 22, 2011 at 5:33 pm
I have an Access application that connects via recordsets and SQL Server authentication using an internal account & login, which is shared by all the users. However, I also have an external Teradata server that these users need to send queries to using their own login information for that system.
I could handle it with OpenRowset, but the query can get complex and it can exceed the 8000 character query limit easily. After much searching, I found that "EXEC (@sSQL) at LinkedServer" doesn't have that limitation, but it only allows you to specify a user name, not user name and password (which the users are entering each time, so I would be sending to the stored procedure as a string). Am I missing some technique or trick that would allow me to send the query to the server similarly to OpenRowset with a connect string?
I could theoretically use sp_addlinkedsrvlogin to create users, but since they all share the same SQL Server authentication login it would overwrite itself if more than one user attempted at the same time.
The only other alternative I can think of is changing my application to using windows authentication and creating an account for each user (which is probably safer from a security perspective anyway - I just didn't want to have to manage accounts and hadn't done it via code before)
August 23, 2011 at 5:27 pm
sleipner (8/22/2011)
I have an Access application that connects via recordsets and SQL Server authentication using an internal account & login, which is shared by all the users. However, I also have an external Teradata server that these users need to send queries to using their own login information for that system.I could handle it with OpenRowset, but the query can get complex and it can exceed the 8000 character query limit easily. After much searching, I found that "EXEC (@sSQL) at LinkedServer" doesn't have that limitation, but it only allows you to specify a user name, not user name and password (which the users are entering each time, so I would be sending to the stored procedure as a string). Am I missing some technique or trick that would allow me to send the query to the server similarly to OpenRowset with a connect string?
I don't think you've missed anything about EXEC(). AFAIK the AS USER syntax only changes the context in which the EXEC text executes locally. It is not meant to be used in conjunction with the AT LINKED_SERVER syntax to translate to a user on the remote server.
I could theoretically use sp_addlinkedsrvlogin to create users, but since they all share the same SQL Server authentication login it would overwrite itself if more than one user attempted at the same time.
You could do that, but if it were me I would use the login from the stored proc param to construct the name of the Linked Server to prevent collisions from concurrent calls. You could optionally drop the Linked Server when you're done if you don't want them persisting but it will add some overhead to also drop the Linked Server when the call completed.
The only other alternative I can think of is changing my application to using windows authentication and creating an account for each user (which is probably safer from a security perspective anyway - I just didn't want to have to manage accounts and hadn't done it via code before)
You could manage it using Windows Groups, either local or Active Directory. That will simplify things because you would not need to create a Server Login or Database User for each person, only one of each for the Windows Group.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply