DSN-less connections (1 Viewer)

[nIGHTmAYOR]

Interesting article (http://www.access-programmers.co.uk/forums/showthread.php?p=760764#post760764).
a bunch of observations though:

1 - the following posted code :

Set tdfCurrent = CurrentDb.CreateTableDef(NameForTableInAccess)
tdfCurrent.Connect = MyConnectionString
tdfCurrent.SourceTableName = TableNameAsNamedInBackEnd
CurrentDb.TableDefs.Append tdfCurrent

so i notice this methode to use TableDefs.Append .. wouldnt that methode still insert full definition and connection string into MSysObject table ?

2 - The following code :

Set MyODBCDb = DBEngine(0).OpenDatabase("MyODBCDbName", dbDriverNoPrompt, True, strConnection)

why would i need to open the backend remotely ? if i am to issue calls to database wouldnt the connection string be enough ? now if i am to open a database remotely wouldnt that apply a lock rendering such database a single user backend ?

3 - the referanced resource :

    Set tdfCurrent = dbCurrent.CreateTableDef(typNewTables(intLoop).TableName)
    tdfCurrent.Connect = "[COLOR=red]ODBC[/COLOR];DRIVER={sql server};DATABASE=" & _
                        DatabaseName & ";SERVER=" & ServerName & _
                        ";Trusted_Connection=Yes;"

so this connection string pass through odbc while may or may not have the connection saved to odbc registry key and thus relying on odbc connectivity and not a bridge like connection to the databse ?

if i am getting this correctly the above methode just provides Non Stored ODBC Like Connectivity , yet will rely again on ms access ultimate security hole called MSysObject

 

[Banana]

Re: Creating and managing DSN-less connections

1) Yes, it does.

2) Well, you still do need the connection string to call the OpenDatabase method. OpenDatabase is analogous to ADO's Connection Object, and as long you hold the reference, it acts as a persistent connection, improving the performance because Access will try to pool the connection based on similarity of connection strings. You certainly can use OpenDatabase method if you do not want to link the tables, preferring to pass only the SQL back and forth.

As for locking, I don't think it pass an exclusive lock, unless you request it as one of argument with Jet's OLE DB provider. It just opens it in shared mode (in Access's case) or open the ODBC database as if it was just another user connecting to it.

3) I'm not sure why MSysObject is considered a security hole. When I create the connection, the connection string for my objects are truncated, omitting the UID and PWD. There is enough information to 'locate' the server but there's no credentials. In my case, the server's location tells nothing because it points to address 127.0.0.1 and isn't stored on the users' machines.

Furthermore, when DSN-less connection is used, there is no calls to registry or files made. It is only made if the parameter 'DSN=XXXX' is given. One could create a DSN without the UID and PWD and write the connection string as:

sConnection="ODBC;DSN=XXXX;UID=XXXX;PWD=XXXX"

which in terms of security is equivalent except for the obvious fact that we're relying on an external file/registry data.

 

[nIGHTmAYOR]

Re: Creating and managing DSN-less connections

usually whenever we get to discuss MSysObject one of us is lost :
* my point of view states that yes MSysObject stores connection string which reveals usernames and passwords to hole exploiters .
** however your point of view is no for the reason that you dont provide the user nore the password , and then you provide them later programaticaly .. now neither this artical nore the referances discribe how .
(remember users are not just trying to obtain a record set programaticaly , but a fully functional linked table)

 

[Banana]

Re: Creating and managing DSN-less connections

Indeed.

I am trying to think of how PWD and UID would actually get stored in the MSysObject, which I've yet to seen it to do as that is usually truncated by Access (at least I think because I don't actually do that myself. It's simply not stored even though I supply the UID and PWD programatically) I do know that if we used DSN and stored password, this is a security hole, but when I used Doug Steele's code, I don't get PWD or UID at all in the MSysObject.

Just to be sure, are we both using same version of Access? (I'm using 2003) Have you actually tried Doug's code and saw the UID and PWD stored anyway?

I also looked around for an option that might affect the storing such data, but couldn't find any....

Anyone else has an idea?

 

[nIGHTmAYOR]

Re: Creating and managing DSN-less connections

Amazing Banana ! you actually never opened MSysObjects table and looked for yourself ? ever wondered how access stores connection strings and passwords to linked tables ? simply in Connect field in plain text in MSysObjects. the methode stated above does nothing but linking tables programaticaly , which even displays a linked table in database window , however you managed to remove uid and password out of connection string in Doug's code and then provide them programaticaly , which is good for security .. yet only intended for ado/dao data manipulation yet how do u create simple queries ? how do your forms/reports access tables ? do you just keep using ado everywhere ? then why not just start developing over MS Visual Basic platform than rather replicating Visual Basic platform work over ms access?

 

[Banana]

Re: Creating and managing DSN-less connections

nIGHTmAYOR-

Did you realize that in my previous post, I indicated that UID and PWD weren't stored at all in my MSysObjects table? Yes, I did open it up both before and after I ran the code to make sure, and at no point were the UID or PWD stored. It gets truncated.

As for the your second question, I've said that tables linked with DSN-less connection act *exactly* as any other linked table- I can query against it, use it for recordsource, and just anything I can do what I can do with other tables.

Which leads me to ask you again-

Did you actually run this code? Did you actually try it yourself?

If you did and you managed to get UID and PWD, then there is something else that may be affecting the behavior because I am telling you that it's just not stored in my MSysObjects table, *despite* me having providing the information programatically and doing nothing to truncate it.

Maybe others would be willing to help us out?


EDIT: While digging around in hope of figuring out why we're not seeing the same behavior, I read the whitepaper, and while it didn't answer the question, I think everyone may be interested to know that Microsoft has provided a mean to force Access to *not* save UID and PWD. This involve creating a table on the server, naming it MSysConf, and giving it the appropriate values so when Jet connects to it, it will query for that table and modify the behavior. The information is all there on the page 8. You can download the whitepaper here.

Too bad it didn't explain about under what other circumstances UID/PWD gets truncated. (and no, I didn't modify the registry setting- I don't even have the appropriate parameters as it was for Windows NT, nor do I use the MSysConf on my MySQL server)


EDIT #2: Looking at Doug Steele's page, he mentioned this on very bottom of page:

Addendum: I received e-mail about this from Bryan Beissel. Bryan indicated that he didn't want to use Trusted Connection. Based on information at Carl Prothman's site, he modified the routine to accept an id and password, and tried to use the following connection string:


tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
";Uid=" & myUserName & _
";Pwd=" & myPassword & ";"

However, Access wouldn't save the information. Bryan came across an article that indicated that you needed to set tdfCurrent.Attributes = DB_ATTACHSAVEPWD in order to have Access save the user id and password information for each table.
I haven't tried this myself (as I always use trusted connection). On first glance, it would seem to be a questionable idea, as you'd end up store the User ID and password in plain text in the Connect property. However, if that doesn't bother you, go for it!

Which suggests to me that Access's default behavior is not to save UID and PWD unless you explicitly set the tabledef's attributes to DB_ATTACHSAVEPWD. I'm trying to find that article, though.

 

[Banana]

Re: Creating and managing DSN-less connections

Which suggests to me that Access's default behavior is not to save UID and PWD unless you explicitly set the tabledef's attributes to DB_ATTACHSAVEPWD. I'm trying to find that article, though.

Doing some more tests, I found that:

1) When a new tabledef is created, running this:

?tdfcurrent.Attributes And DbAttachSavePWD
0

2) When we hit the line where we set the Connect property of the variable tdfCurrent:

?tdfcurrent.Connect
<Full connection string with UID and PWD included>

3) When the variable is attached to the TableDefs collection of variable dbCurrent (note that this is a variable, not CurrentDb() function), we get:

?tdfCurrent.Connect
<Full connection strings with UID and PWD included, but parameters get swapped around>

Weird that Access would swap it around... the old connection string had UID and PWD at last, but after attaching to the collection, the UID and PWD is just after the Driver argument. Curious.

4) At same point (e.g. after the attaching line as in 3):

?CurrentDb.TableDefs("NameOfTableJustAttached").Connect
<Connection string truncated without the UID and PWD>

So while the variable referring to the table has the full connection string, the Access does not preserve the UID & PWD when it adds to the TableDefs collection, and likewise, the MSysObject does not have the UID & PWD in the Connect property.

5) Obviously, once the function finishes executing, the variables goes out of scope and thus UID and PWD is lost. (It helps to recall that Access will save the UID and PWD internally and inaccessible via VBA once a successful connection has been made and will re-use it for any tables where the connection string is sufficiently similar [usually by checking if it calls the same database name])

6) Closing the DB and reopening the db and trying to open the tables directly without running the function results in an error, as expected.


And I tried linking the table with DSN. If I link the same table but do not check the "Save Password" box, it pretty much behaves the same way as I've described. The default is not to have it saved. I've been unable to locate any options that would govern this, and cannot recall having configured Access to behave this way.

Yet, the behavior I've described seems to be at odds with what is reported by others about saving passwords being the default.

Can anyone please confirm/deny/clarify this?

 

[Banana]

*bump*

I'm hoping someone could at least confirm/deny/clarify the behavior of linking and saving of passwords.

 

[AxGryndr]

But if you create a pass through query doesn't that also keep the UID and PSW?

 

[Banana]

Good question! I could check into that.

That said, all of my queries, whether PT or not, are based on a template where connect string is always supplied but I only save driver, server and database parameters which is sufficient for Access to multiplex on top of prior open connection so there is really no need to save UID and PWD for any queries, provided that you've already created a connection (via OpenDatabase method for example).