Solved

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

Posted on 2014-02-06
26
500 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
  • 10
  • 6
  • 4
  • +2
26 Comments
 
LVL 10

Expert Comment

by:HuaMinChen
Comment Utility
0
 
LVL 12

Author Comment

by:Koen Van Wielink
Comment Utility
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 10

Expert Comment

by:HuaMinChen
Comment Utility
Within Sql server management studio, are you able to connect to DB, using Sql server authentication?
0
 
LVL 12

Author Comment

by:Koen Van Wielink
Comment Utility
Yes, that all works, as does the ODBC connection when we test it independently.
0
 
LVL 10

Expert Comment

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

Author Comment

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

Expert Comment

by:HuaMinChen
Comment Utility
Has there been any change recently? Did you encounter the same issue before now?
0
 
LVL 12

Author Comment

by:Koen Van Wielink
Comment Utility
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 10

Expert Comment

by:HuaMinChen
Comment Utility
Are you using server name or IP to connect to database?
0
 
LVL 12

Author Comment

by:Koen Van Wielink
Comment Utility
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 12

Author Comment

by:Koen Van Wielink
Comment Utility
Tried both server name and IP address. Same result.
0
 
LVL 10

Expert Comment

by:HuaMinChen
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 12

Author Comment

by:Koen Van Wielink
Comment Utility
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.
Comment Utility
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 12

Author Comment

by:Koen Van Wielink
Comment Utility
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.
Comment Utility
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 500 total points
Comment Utility
> 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
Comment Utility
0
 
LVL 18

Expert Comment

by:vasto
Comment Utility
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 12

Author Closing Comment

by:Koen Van Wielink
Comment Utility
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
Comment Utility
>  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 12

Author Comment

by:Koen Van Wielink
Comment Utility
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
Comment Utility
>  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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now