0

I am trying to connect my .NET web service to a SQL Server via ODBC Connection.

Here is my ODBC Connection

enter image description here

Then I created a Data Connection in the Server Explorer via VS2017:

enter image description here

In the web.config, I have the following:

<connectionStrings>
<add name="MyConnection" connectionString="Dsn=NGDEV" providerName="System.Data.SqlClient"/>
</connectionStrings>

In my code, I connect by

using(OdbcConnection connection = new OdbcConnection(ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString))
using(OdbcCommand command = connection.CreateCommand() {
command.CommandText = "SELECT * FROM table1";
connection.Open(); // This is where I get the error
}

The error message is:

Login failed for user ''.

I noticed that the user is blank. I don't understand why. I know that the connection works because I tested it.

Peter Sun
  • 1,675
  • 4
  • 27
  • 50

2 Answers2

0

Provider name is System.Data.SqlClient, but you are using ODBC.

Andrey Belykh
  • 2,578
  • 4
  • 32
  • 46
0

You can't get back the username and the password from a stored ODBC connection. If you open Regedit and look at the key 

HKEY_CURRENTUSER\Software\ODBC\ODBC.INI\NGDEV

you will never find a key for your password and also the LastUser key doesn't mean that this value is passed to your code when you call that DSN.
Instead it is used to initialize the dialog that ask the password when you reconfigure again the saved ODBC.

You need to change your connectionstring to something like this

<add name="MyConnection"  
           connectionString="Dsn=NGDEV;Uid=sa;Pwd=***whatever***"  
           providerName="System.Data.Odbc"/>

This of course open the door to a lot of security problems.

  • First: You should never use the sa account to connect to your database from code. This user is reserved only for administrative tasks (and also in that case is not a wise choice) because it can access not just your database but everything stored in that server. A simple mistep and you can look at serious problems.
  • Second: having a clear text password written in your config file is another problem. You can try to encrypt the config section but it would be a lot better if you can connect using trusted connection.
Steve
  • 213,761
  • 22
  • 232
  • 286
  • Usually, the ODBC connection is actually saving the user ID. For the password, some ODBC providers have option t save it (usually it dasiplays some kind of warning) – Andrey Belykh Dec 14 '17 at 21:54
  • @AndreyBelykh well of course I cannot be certain of the behavior of every ODBC provider out there, but with SQL Server if you try to build your own ODBC entry with the administrative tool your write an entry in the registry section mentioned above and you don't have a password. If you try to reconfigure the entry you will be asked again for the password. – Steve Dec 14 '17 at 22:01
  • It saves the UID if you write a FILE DSN, not a System DSN as the OP does. Still no password – Steve Dec 14 '17 at 22:10