Re: Save ADP Connection to UDL -> Data Link file is inaccessible error

Mary Chipman [MSFT] wrote:
First of all, you do not want to be using a UDL file to connect to
your SQL Server. This is a really, really bad idea. UDL's are stored
in plain text--just right-click on one to open it in Notepad. 

I know what UDL files are.

> You are
jeopardizing security on your server by storing user names and
passwords in them. I don't believe Access ADPs support UDLs anyway,
which is a good thing.

All the work stations using this application are on a corporate LAN behind a firewall. The assumption is that the workstations are already secured. Besides, credentials are stored in a UDL file only if SQL Server Authentication is used.

> If you want to persist the connection string
for an ADP then consider the registry or some other location that
offers some level of protection.

The registry offers no more protection than an NTFS file system does, except possibly by obscurity.

> Even better, prompt the user for 
connection information at runtime and don't save the connection string
anywhere.

The users will consider this an unacceptable nuisance, not to mention that users writing down servers, user names, and passwords on sticky notes is no more secure than just storing the credentials in a UDL.

On Thu, 26 Jan 2006 15:10:56 -0500, Beowulf
<beowulf_is_not_here@xxxxxxxxxxx> wrote:

I use the code below to save an ADO Connection object's connection string to a UDL file. If I use a connection object connected directly to my SQL Server database, I can double click the UDL file to open up in the "Datalinks" dialog.

Direct connection to SQL Server:

"Provider=SQLOLEDB.1;Persist Security Info=True;Data Source=MyServer;User ID=luser;Password=pwd;Initial Catalog=MyDatabase;"

If I pass in Application.CurrentProject.Connection from inside an ADP project, I get the error message "Properties you entered cannot be saved because the Data Link file is inaccessible. Ensure that the hard drive or network share is still available." when I double-click the UDL file.

The connection string causing the error message looks like this (i.e., Connection to SQL Server through Access):

"Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=True;Data Source=MyServer;User ID=luser;Password=pwd;Initial Catalog=MyDatabase;Data Provider=SQLOLEDB.1"

I thought the problem was the Data Provider= clause, but if I open the problem UDL file in WordPad and change the connection string to this:

"Provider=MSDataShape;Data Provider=SQLOLEDB.1;Password=pwd;Persist Security Info=true;User ID=luser;Initial Catalog=MyDatabase;DataSource=MyServer"

then I am able to double-click the UDL file and open it in the "Datalinks" dialog again. Why can't a Microsoft.Access.OLEDB.10.0 connection string be stored and then opened up as a UDL file? 
.

Relevant Pages