Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL error saying only Windows authentication allowed, DSN test ok.

Posted on 2014-02-06
26
Medium Priority
?
552 Views
Last Modified: 2014-02-11
The supplier of our ERP system has provided us with a small program that uses an ODBC connection to connect to the system's database. The ODBC is setup as a system DSN using SQL server on a Windows 2003 R2 machine, connecting to a database running on MSSQL 2008 R2. The DSN connection test is successful using a defined SQL username and password (so no windows authentication).
However, when we start the application, an error log is generated stating the following error:

ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''.

When I checked with the supplier they told me that they are aware some ODBC connections don't work due to a windows authentication issue and they haven't been able to figure it out, and gave me an alternative way to connect. But because this forces me to store the username and password in plain text I'm reluctant to use this. So I did a bit more digging, and in the MSSQL error log I found the following message:

2014-02-07 10:18:11.95 Logon       Error: 18456, Severity: 14, State: 58.
2014-02-07 10:18:11.95 Logon       Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 192.168.x.xx]

We double checked the security settings are the server is definitely configured in mixed mode.
Does anyone have any idea why this error might be shown?
0
Comment
Question by:Koen Van Wielink
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 4
  • +2
26 Comments
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 39841070
0
 
LVL 13

Author Comment

by:Koen Van Wielink
ID: 39841114
Dear HuaMinChen,

Thanks for the reply, but as I pointed out, the setup is already, and has always been, mixed mode. That's why we don't understand the error message in the error log.
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 39841119
Within Sql server management studio, are you able to connect to DB, using Sql server authentication?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 13

Author Comment

by:Koen Van Wielink
ID: 39841121
Yes, that all works, as does the ODBC connection when we test it independently.
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 39841151
Can you confirm if you're connecting to the same database, that is having Sql server authentication?
0
 
LVL 13

Author Comment

by:Koen Van Wielink
ID: 39841165
Yes, i am able to access the database and relevant tables with the particular SQL login used by the DSN.
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 39841195
Has there been any change recently? Did you encounter the same issue before now?
0
 
LVL 13

Author Comment

by:Koen Van Wielink
ID: 39841201
Nope. No changes. We did not encounter this issue before, because this application has only just been released by our ERP vendor. This is all still on a test setup. We have been using the same username and password successfully (and still are) using the old configuration. That's why I'm 100% sure that SQL authentication is active and working.
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 39841223
Are you using server name or IP to connect to database?
0
 
LVL 13

Author Comment

by:Koen Van Wielink
ID: 39841224
Just to add, if we replace

"DSN=ourDNSConnection"

with

"Data Source=OurServerName;Initial Catalog=OurDatabaseName;Connect Timeout=5;User Id=UserName;Password=OurPassword"

in the connection parameters the application does work. So again, the user setup in SQL is correct, and the application functions, but somehow when a DSN is used the credentials don't work.
0
 
LVL 13

Author Comment

by:Koen Van Wielink
ID: 39841228
Tried both server name and IP address. Same result.
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 39841239
This is a problem with DSN. So you can instead use this

"Data Source=OurServerName;Initial Catalog=OurDatabaseName;Connect Timeout=5;User Id=UserName;Password=OurPassword"

Open in new window

0
 
LVL 13

Author Comment

by:Koen Van Wielink
ID: 39841254
Nice copy/paste of my last post HuaMinChen. As I explained in my original post, I do not want to use this method as it forces me to put the username and password in plain text in the startup file. I just put it up as confirmation that everything is working as it should when DSN is not used.
The whole point of this post is to find out WHY the DSN does NOT work.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39852095
This may sound if I'm going to go off on a tangent on this -- but is Named Pipes (NP) enabled on the SQL Server and or clients?

What happens if you turn it off on the SQL Server?

NP needs to be registered with the domain, but many times SQL no longer does it it with AD.

So it may be an authentication of NP over TCP.
0
 
LVL 13

Author Comment

by:Koen Van Wielink
ID: 39852121
Hi Jim,

Thanks for replying. Can you tell me where exactly I should be checking this? Is it in the TCP/IP settings?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39852140
It will be in the SQL Server configuration manager.

The way Named Pipes works is that it redirects the authentication to some DC. The DC then gives a long term token to the SQL Server from the authenticated client/app/service. But if any of the connections between the  DC, client, or server drops that  token becomes invalid and is not renewable with out a restart of authentication usually involving an additional restart of the client/app/service.

Real PITA, I automatically turn of NP first thing.
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 2000 total points
ID: 39852173
> The whole point of this post is to find out WHY the DSN does NOT work.

Because you hope to use the password stored in ODBC connection, but this is not possible, by design. See the following thread:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/221ddfa8-d950-4f6d-a136-dd0737ba09fc/saving-odbc-password?forum=sqldataaccess

If you configure your ODBC, specify the password, test, then close, and then reopen it, the password is not there, is it? So it's not there for the application as well.

The error message in the error log, surely, is misleading.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39852208
0
 
LVL 18

Expert Comment

by:vasto
ID: 39852264
You can try this:
find the DSN file, it should be something like filename.dsn, edit it with notepad and add a line
Pwd=yourpassword

Then try to connect again. If it works then call your ERP vendor and explain that the problem is not on your side.  Of course you cannot leave the DSN file with the password but you can prove that the problem is not in SQLServer.
0
 
LVL 13

Author Closing Comment

by:Koen Van Wielink
ID: 39852305
Ok, that would indeed explain everything. I assumed the password was stored, even though it wasn't displayed. I guess I'm stuck with having the password in plain text for now. Will make sure the batch file is properly protected.

To Vasto, thanks for the suggestion of using file DNS, but that would basically result in the same solution as having the connection string including username and password in the application config file. We've already proven that this works, so the vendor knows the problem is not with our SQL setup. They actually acknowledged that they still had DSN connection issues with some customers, I'm just trying to help them to get to the bottom of things. Now I'm curious if they have this working for other customers, which they claim they do....

Thanks for the help everyone!
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39852339
>  Now I'm curious if they have this working for other customers, which they claim they do....

It's very likely that storing the password in DSN was possible in the past (i.e. older versions of Windows), when security concerns were not as high.

It looks like you are under impression that storing the password in DNS somehow would be more secure than in the file. All DNS parameters are stored in the registry (under hklm\software\odbc) in plain text form, so even if your plan worked, the only difference would be that it would be in the registry rather than in the file.

@Jim P., (1) named pipes is network protocol, and it has nothing to do with any authentication (2) with sql server-based authentication nothing is forwarded to DC anyways (3) windows-based authentication works equally well in workgroups, domain is not necessary.
0
 
LVL 13

Author Comment

by:Koen Van Wielink
ID: 39852345
Hi Vadim,

The reason I feel it's more secure is that the application file which now has the connection string stored in it has to be located in a folder to which all users have access. In comparison the DSN is added on another server only accessible by sysadmins. As such, I feel it's more secure. Could be subjective I guess,
Anyway, I like solving puzzles so a lot of this is just to satisfy my own curiosity.
Thanks again.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39852383
>  application file which now has the connection string stored in it has to be located in a folder to which all users have access

Make it hidden :-)
0
 
LVL 18

Expert Comment

by:vasto
ID: 39852388
The only way they can have DSN file working without setting a password programmatically is by using Integrated security. There are plenty of applications , which are using ODBC and they work fine. However they provide a way to set and preserver the password.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question