Unable to use System DSN

[Dan]

We are trying to use a System DSN to connect to a SQL
Server data source using an ASP.net application.

We create the DSN and configure it to use SQL
authentication. The DSN is tested to make sure it works.

Our ASP page contains the following simple code:

Dim connString As String = "DSN=myDSN;"
Dim conn As OdbcConnection = New OdbcConnection(connString)
Try
conn.Open()
Catch ex As Exception
Console.WriteLine(ex)
End Try

When this code is executed, the following error is
generated:

Message "ERROR [28000] [Microsoft][ODBC SQL Server Driver]
[SQL Server]Login
failed for user '(null)'. Reason: Not associated with a
trusted SQL Server
connection.

ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL
Server]Login failed
for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.

Our issues are:
1) We want to use the authentication mode configured in
the DSN we set up (not trusted in this case).
2) We do not want to pass the user name and password in
the connection string.
3) We do not want to have to grant special privileges to
the ASP.net account in order to access a System DSN.

Does anyone have ideas on how to use a System DSN with ASP
in this context?

Any assistance is appreciated.

 

[Evert Timmer]

As I see it, your problem has to do with the fact that it is impossible to
have login information configured with your System DSN without having to
connect with trusted connections.

TMHO:
If you want to login with the same accountinfo for every user which starts
your app, and NOT with an NT Account, i think you should reconsider your
strategy regarding the passing of password & username in the
connectionstring. I really believe the best way to implement this is to have
a simple sql account with ONLY the privileges set that are needed to run
your app. I would rule out direct modifications agains SQL tables by
granting read-only access to the tables for this user, and executing stored
procedures for all data-modifying actions. Also do not build your SQL
statements directly inside your app, making it vulnerable to attacks, but
use stored procedures for database-access at all times.

Evert

 

[Dan]

Evert,

Thanks for the reply but I don't believe what you are
proposing will work for us.

As we are distributing this application, there is no way
to know in advance what the SQL user account for
connection will be. Further, some users may be utilizing
trusted and/or non-trusted connections.

I find it quite odd that Microsoft does not provide a
secure method to use ODBC connection information without
having to store the user name and password in an unsecured
file or other similar location.

If you can think of anything else, I would like to know
about it.

-----Original Message-----
As I see it, your problem has to do with the fact that it is impossible to
have login information configured with your System DSN without having to
connect with trusted connections.

TMHO:
If you want to login with the same accountinfo for every user which starts
your app, and NOT with an NT Account, i think you should reconsider your
strategy regarding the passing of password & username in the
connectionstring. I really believe the best way to implement this is to have
a simple sql account with ONLY the privileges set that are needed to run
your app. I would rule out direct modifications agains SQL tables by
granting read-only access to the tables for this user, and executing stored
procedures for all data-modifying actions. Also do not build your SQL
statements directly inside your app, making it vulnerable to attacks, but
use stored procedures for database-access at all times.

Evert

We are trying to use a System DSN to connect to a SQL
Server data source using an ASP.net application.

We create the DSN and configure it to use SQL
authentication. The DSN is tested to make sure it works.

Our ASP page contains the following simple code:

Dim connString As String = "DSN=myDSN;"
Dim conn As OdbcConnection = New OdbcConnection (connString)
Try
conn.Open()
Catch ex As Exception
Console.WriteLine(ex)
End Try

When this code is executed, the following error is
generated:

Message "ERROR [28000] [Microsoft][ODBC SQL Server Driver]
[SQL Server]Login
failed for user '(null)'. Reason: Not associated with a
trusted SQL Server
connection.

ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL
Server]Login failed
for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.

Our issues are:
1) We want to use the authentication mode configured in
the DSN we set up (not trusted in this case).
2) We do not want to pass the user name and password in
the connection string.
3) We do not want to have to grant special privileges to
the ASP.net account in order to access a System DSN.

Does anyone have ideas on how to use a System DSN with ASP
in this context?

Any assistance is appreciated.

.