ConnectionString using DSN not working

[Fendi Baba]

I have been using SqlConnection .NET strings in my web,config and my
application is working finr. However due to requirements, I am forced
to change the connection string to use DSN and I cannot connect to the
data. Can anyone point out what is wrong with my settings.

Here is the SqlConnection in my web.config

<add name="LocalSqlServer" connectionString="server=EFFENDI
\SQLEXPRESS;uid=CoopAdmin;pwd=pass;database=coopgown"
providerName="System.Data.SqlClient" />


And here is the one using DSN

<add name="LocalSqlServer"
connectionString="Dsn=DBADGRAD;uid=CoopAdmin;pwd=pass"
providerName="System.Data.Odbc" />

When I try to open the Asp.net application Configuration pages, I get
the following error mesages

An error occurred while attempting to initialize a
System.Data.SqlClient.SqlConnection object. The value that was
provided for the connection string may be wrong, or it may contain an
invalid syntax. Parameter name: connectionString

Any ideas?

Thanks in advance.

 

[Norman Yuan]

You did not show your code, but since you use ODBC provider, you should use
OdbcConnection and other related data access classes in System.Data.Odbc
namespace instead of classes in System.data.SqlClient namespace.

 

[Fendi Baba]

Hi Norman

That could be an issue. But I'm trying to access the create user pages
in ASp.net configuration. I assume we dont have to change anything
here since this page is system default.

I should change the other pages as you righly pointed out should use
the System.Data.ODBC namespace.

 

[Mary Chipman [MSFT]]

DSNs are not supported for SqlClient, mainly because they're a huge
security vulnerability. When connecting to a SQL Server database from
your .NET code you should always use SqlClient, not odbc or oledb.
SqlClient gives you functionality that the other data providers don't.
The only exception to that rule would be if you were connecting to
SQLS v6.5 or earlier, which doesn't seem to be the case here.

--Mary

 

[Fendi Baba]

Ok, But when I created tableAdapters to use connectionstring created
using DSN, it work normally. While not doubting whatyou've said, I am
jsut trying to figure out what to tell my bosses why I cant use DSN...

 

[Paul Clement]

¤ Ok, But when I created tableAdapters to use connectionstring created
¤ using DSN, it work normally. While not doubting whatyou've said, I am
¤ jsut trying to figure out what to tell my bosses why I cant use DSN...
¤

ODBC is legacy technology. The only reason to support it is if you have legacy code or no native
..NET provider for the database that you are working with. Native .NET provider libraries do not
typically support the DSN feature, which was developed for ODBC.

If the reason for using a DSN is to hide the details of the connection, you can encrypt the
connection string information and read it from a .config file instead.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Fendi Baba]

¤ Ok, But when I created tableAdapters to use connectionstring created
¤ usingDSN, it work normally. While not doubting whatyou've said, I am
¤ jsut trying to figure out what to tell my bosses why I cant useDSN...
¤

ODBC is legacy technology. The only reason to support it is if you have legacy code or no native
.NET provider for the database that you are working with. Native .NET provider libraries do not
typically support theDSNfeature, which was developed for ODBC.

If the reason for using aDSNis to hide the details of the connection, youcan encrypt the
connection string information and read it from a .config file instead.

Paul
~~~~
Microsoft MVP (Visual Basic)

Paul

Thanks for the answers. Could you lead me to a resource which states
this? My infrastructure folks is concerned about moving away from ODBC
and has asked me for a documentation.

Regards

 

[William \(Bill\) Vaughn]

I agree. ODBC is outdated technology and should only be used if there are no
other alternatives. As time goes on you'll find that more and more DBMS
vendors drop support for ODBC. Yes, it has been implemented on some engines
to provide backward compatibility or to permit access by legacy
applications. In many cases, these ODBC interfaces implement a subset of the
broad spectrum of functionality exposed by the engine.

Is this documented? Like in one of the dozen books I've written? Ah, yes.

--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition

-----------------------------------------------------------------------------------------------------------------------

¤ Ok, But when I created tableAdapters to use connectionstring created
¤ usingDSN, it work normally. While not doubting whatyou've said, I am
¤ jsut trying to figure out what to tell my bosses why I cant useDSN...
¤

ODBC is legacy technology. The only reason to support it is if you have
legacy code or no native
.NET provider for the database that you are working with. Native .NET
provider libraries do not
typically support theDSNfeature, which was developed for ODBC.

If the reason for using aDSNis to hide the details of the connection, you
can encrypt the
connection string information and read it from a .config file instead.

Paul
~~~~
Microsoft MVP (Visual Basic)

Paul

Thanks for the answers. Could you lead me to a resource which states
this? My infrastructure folks is concerned about moving away from ODBC
and has asked me for a documentation.

Regards

 

[Paul Clement]

¤ On Apr 18, 8:40 pm, Paul Clement
¤ >
¤ > ¤ Ok, But when I created tableAdapters to use connectionstring created
¤ > ¤ usingDSN, it work normally. While not doubting whatyou've said, I am
¤ > ¤ jsut trying to figure out what to tell my bosses why I cant useDSN...
¤ > ¤
¤ >
¤ > ODBC is legacy technology. The only reason to support it is if you have legacy code or no native
¤ > .NET provider for the database that you are working with. Native .NET provider libraries do not
¤ > typically support theDSNfeature, which was developed for ODBC.
¤ >
¤ > If the reason for using aDSNis to hide the details of the connection, you can encrypt the
¤ > connection string information and read it from a .config file instead.
¤ >
¤ > Paul
¤ > ~~~~
¤ > Microsoft MVP (Visual Basic)
¤
¤
¤ Paul
¤
¤ Thanks for the answers. Could you lead me to a resource which states
¤ this? My infrastructure folks is concerned about moving away from ODBC
¤ and has asked me for a documentation.
¤

Well actually I guess you could say that I'm the resource. ;-)

I don't believe there is any official statement from Microsoft on this topic. ODBC has been a widely
used and successful database standard for many years and there are some database vendors that only
provide ODBC drivers for their product. This is why Microsoft provides a managed code library for
..NET that wraps the ODBC functionality.

The SQL Server ODBC technology is still supported, even in the 64-bit world. However, you will not
really see any enhancements that parallel those for the .NET native SQL Server library.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[William \(Bill\) Vaughn]

Yes of course, but you'll agree that the ODBC drivers do not (typically)
support full functionality of target data providers. They also expose
different security risks, different (and more difficult) deployment
strategies and other issues that the native providers don't.



--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Paul Clement]

¤ Yes of course, but you'll agree that the ODBC drivers do not (typically)
¤ support full functionality of target data providers. They also expose
¤ different security risks, different (and more difficult) deployment
¤ strategies and other issues that the native providers don't.

Yup, all true, especially in the case of Jet and I would assume SQL Server as well.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Fendi Baba]

Thank everyone. One of the concern we have is the fact that using not
using ODBC means connecting to te server direct. This works out nicely
for development environment but when we move to production where the
database server is in a server famr, we have to change the config file
to change to the server accordingly. Is there a way we can "make" this
server change automated? My administrators would definitely appreciate
that.

Regards

 

[William \(Bill\) Vaughn]

Over the years application designers have tried to create generic
applications that can work regardless of which backend database engine is
accessed. This assumes that each DBMS is the same--supports the same SQL
dialect (or at least a common subset), other functionality, admin processes,
connection strategies and other aspects. For the most part they don't. For
example, Oracle, SQL Server and JET all use different approaches to stored
procedures and SQL. All have different admin functions (like backup,
restore, user account management, security), different performance tuning
approaches and on and on.

This means that when you use an "OSFA" (one-size-fits-all) generic interface
like ODBC or OLE DB (and there are others) you must go to great lengths to
take advantage of the specific features you pay for when you buy SQL Server
or other SQL engines.

When you use SqlClient or OracleClient your code talks directly to the SQL
Server-specific TDS data stream or the Oracle-specific data stream.
Performance of ODBC and OLE DB is not nearly as good nor as flexible. If you
want to access all of the features of any serious DBMS engine, you can't
depend on ODBC or OLE DB to expose them.

Using SqlClient or other provider-specific interface, changing the
connection string to address the new targeted DBMS is something that can and
is done quite often as long as you design in the approach. Yes, the
TableAdapter and other wizards don't make this easy, but it can be done.
Consider that the application properties are accessible via code--they
include the connection string used to access the DBMS. As long as the schema
and rights of the target DBMS match the development system your application
should work fine.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------