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:
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:
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?
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?
ASKER
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.
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.
Within Sql server management studio, are you able to connect to DB, using Sql server authentication?
ASKER
Yes, that all works, as does the ODBC connection when we test it independently.
Can you confirm if you're connecting to the same database, that is having Sql server authentication?
ASKER
Yes, i am able to access the database and relevant tables with the particular SQL login used by the DSN.
Has there been any change recently? Did you encounter the same issue before now?
ASKER
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.
Are you using server name or IP to connect to database?
ASKER
Just to add, if we replace
"DSN=ourDNSConnection"
with
"Data Source=OurServerName;Initi
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.
"DSN=ourDNSConnection"
with
"Data Source=OurServerName;Initi
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.
ASKER
Tried both server name and IP address. Same result.
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"ASKER
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.
The whole point of this post is to find out WHY the DSN does NOT work.
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.
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.
ASKER
Hi Jim,
Thanks for replying. Can you tell me where exactly I should be checking this? Is it in the TCP/IP settings?
Thanks for replying. Can you tell me where exactly I should be checking this? Is it in the TCP/IP settings?
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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!
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!
> 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.
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.
ASKER
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.
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.
> 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 :-)
Make it hidden :-)
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.
http://www.lansweeper.com/