Help w/ OPENROWSET
-
Dear All,
I have the following scenario:
I am running a VB application on MSDE which at some point connects to the Internet using a dialup connection. Once the connection is successful, the application initializes an ADODB connection on a remote SQL Server (Static IP Address) and the user gets propmted for authentication by the Firewall. After authentication the user excutes a SP that resides on the MSDE Database. The SP uses the OPENROWSET function where I pass my connection string, in order to exchange data with the remote SQL Server. However, I do get a "Timeout Expired" error.
Bear in mind that I have set the ADODB connection "ConnectionTimeout" property to 60 secs instead of the Default 15 secs. Do I have to set a connection timeout property within the using the "provider_string" parameter of the OPENROWSET function?
MSDE has a BLANK SA Password while the remote SQL Server has a strong SA Password. Does this matter? The user connects to both servers as SA.
-
Password wont matter - unless is not really blank! I'd suggest changing that to a strong password as well.
-
Are you trying to authenticate directly to SQL
? in that case you have to have that port open in your firewall 
* Noel -
You may want to reconsider using the sa userid for an application. It is potentially dangerous even if your application is immune to sql injection attacks. You risk the user finding out the sa password (even the strong one) and exposing your data to potential damage, either intentional or accidental. Noone (not even the DBA) should be using 'sa' especially as a matter of course. All access ideally should be via some id that is tied to one and only one person who is responsible for the use of that id. If you want an application to access the database always using the same id in order for connection pooling to work- fine but the user still needs to authenticate to the application with their own personal id of sorts. Even if this doesn't happen I encourage you not to choose 'sa' (or any id in the sysadmin group) as the the userid of choice. Create some id specific to the application and only give necessary authority. Even db_reader and db_writer sucks but if you don't use stored procs for all access then you end up with this scenerio. Its far from acceptable since users have access to all tables using any applcation they want to use (QA, EXCELL, ACCESS, tons of off the shelf application allow users to do anything they want to the data)
Enough of the rant. Just trying to pass on something to think about.
Francis
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply