Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • Last Modified:

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

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
Koen Van Wielink
Asked:
Koen Van Wielink
  • 10
  • 6
  • 4
  • +2
1 Solution
 
HuaMinChenBusiness AnalystCommented:
0
 
Koen Van WielinkIT ConsultantAuthor Commented:
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
 
HuaMinChenBusiness AnalystCommented:
Within Sql server management studio, are you able to connect to DB, using Sql server authentication?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Koen Van WielinkIT ConsultantAuthor Commented:
Yes, that all works, as does the ODBC connection when we test it independently.
0
 
HuaMinChenBusiness AnalystCommented:
Can you confirm if you're connecting to the same database, that is having Sql server authentication?
0
 
Koen Van WielinkIT ConsultantAuthor Commented:
Yes, i am able to access the database and relevant tables with the particular SQL login used by the DSN.
0
 
HuaMinChenBusiness AnalystCommented:
Has there been any change recently? Did you encounter the same issue before now?
0
 
Koen Van WielinkIT ConsultantAuthor Commented:
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
 
HuaMinChenBusiness AnalystCommented:
Are you using server name or IP to connect to database?
0
 
Koen Van WielinkIT ConsultantAuthor Commented:
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
 
Koen Van WielinkIT ConsultantAuthor Commented:
Tried both server name and IP address. Same result.
0
 
HuaMinChenBusiness AnalystCommented:
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
 
Koen Van WielinkIT ConsultantAuthor Commented:
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
 
Jim P.Commented:
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
 
Koen Van WielinkIT ConsultantAuthor Commented:
Hi Jim,

Thanks for replying. Can you tell me where exactly I should be checking this? Is it in the TCP/IP settings?
0
 
Jim P.Commented:
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
 
Vadim RappCommented:
> 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
 
vastoCommented:
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
 
Koen Van WielinkIT ConsultantAuthor Commented:
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
 
Vadim RappCommented:
>  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
 
Koen Van WielinkIT ConsultantAuthor Commented:
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
 
Vadim RappCommented:
>  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
 
vastoCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now