July 16, 2009 at 4:46 pm
I am encountering the error in the subject of this message:
"I getting this error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection"
I googled on this and found many references to the causes/solutions on this and other sites. However, my scenario is a little different from most people's situations and I have not found a discussion based on a similar situation, so I would like to pose it here.
I have a .Net application on a server that is trying to connect to a SQL Server database on a separate server. That separate server (running MSSQL) is a "black box" provided by another vendor, so we are limited in changing anything on that server.
The "black box" SQL Server is NOT on the domain and is set for Windows Authentication Mode. Obviously, this does not look like a good combination for accessing the databases remotely.
Essentially, the only type of accounts that can be added to the SQL security are local Windows accounts on that server, since it can't use domain accounts and it can't use SQL accounts.
I was/am hoping that "pass through" authentication will work with SQL Server like it works with Windows file security. I created a local Windows user on the .Net server that matched username and password to an existing local Windows user on the SQL server that has access to the database. I then attempt to connect to the database server using that local Windows account. When I do that (via the .Net application or even attempting to create an ODBC connection with ODBC manager), I get the error message in the subject line of this post.
I'm afraid the answer is that this will not work. But I am hoping to get some confirmation from someone that knows for a fact if (1) this will never work or (2) this should work and I must have something misconfigured, so keep at it.
Thanks,
Gregg
July 16, 2009 at 9:35 pm
Pass-thru does not work. Period.
Changing the Black Box machine from integrated to mixed mode wouldn't change how that SQL server works with its current apps, and is really not a problem.
I'm not sure you really have a choice.
CEWII
July 28, 2009 at 11:28 pm
I have one question
In one of the servers which we use ,
I can see the in the sql server logs as the connection is not trusted on
one of the logins,
Is there any way so that we can track this for all those logins and automate an email message to our email id by having server level trigger
Regards
chethan
July 29, 2009 at 12:52 pm
I'm not sure you can accomplish this with SQL 7 or 2000, in SQL 2008 you can using a LOGON trigger and you could probably trace it using sp_trace_create in 2005 and above, you'll have to research for 7 and 2000..
CEWII
July 30, 2009 at 12:12 pm
If I understand you right, you said the database server is not part of the domain. If that is the case the you can not use windows Authenication. You would need to use sql Authenication but the problem with that is you said SQL is not set to run mixed mode which it would need to be. The statement you made about the only accounts that can be added are local windows accounts is not an acurate statement. When these local accounts are added to SQL they are assentialy SQL accounts. In your connection string you should not be running it as trusted connection. You would need to speacify the account name and password in the connection string. The draw back to that would be it is passed in plain text over the wire unless you are using SSL.
September 1, 2009 at 12:04 pm
mforbes (7/30/2009)
If I understand you right, you said the database server is not part of the domain. If that is the case the you can not use windows Authenication. You would need to use sql Authenication but the problem with that is you said SQL is not set to run mixed mode which it would need to be. The statement you made about the only accounts that can be added are local windows accounts is not an acurate statement. When these local accounts are added to SQL they are assentialy SQL accounts. In your connection string you should not be running it as trusted connection. You would need to speacify the account name and password in the connection string. The draw back to that would be it is passed in plain text over the wire unless you are using SSL.
Are you sure about this? If I am not mistaken, this is how SQL Server and Webservers in DMZ are set up with Windows Authentication. The Trick is to create the same username with same password in SQL Server Locally and the Web servers locally. You create a Login for the Local windows account and then use Integrated security in your IIS with the application pool running under the Account created in the web server. You do not need to specify the Password in the Config file at all. This will work with the same Token method as a normal Windows Authentication.
Just my 2 cents
-Roy
September 2, 2009 at 7:16 am
Hi !!
answering the question of chethan, I have a “logon failure” alert in my sql 2000 and 2005 servers. This alert send me a mail practically at the same moment in that the failure takes place (obviously there are a little delay due to Exchange server).
Simply you need an Operator and an Alert. In the configuration of the alert I put this:
Type: SQL Server event alert
Database name:all databases
Severity:014 – Insufficient Permission
Message text: Login failed for user
In my case, I want to know all logins failures of any user (for that reason my message text doesn’t indicate any user in particular). Perhaps in your case or the case of another person, this configuration is something different.
Helinille
(sorry if my English isn’t very good.)
September 10, 2009 at 8:49 am
As far as I know, Windows authentication will not work as long as the two servers are on different domains and the domains are not trusted. Even if they were trusted, you would need to add the foreign domain user in the logins for the database. Cross-domain access is a pain, on purpose. I believe you will need to use a database login that you will pass in with the connection string. I don't know of any other way in that scenario.
September 10, 2009 at 12:18 pm
I believe John is right on with his comments.
CEWII
September 14, 2009 at 9:25 am
I'm not sure I've fully followed all the discussion, but I do have Logins from different domains coming into my SS 2000.
I have gotten the error mentioned with my own login, which I use all the time. It happened when I logged in from a different machine, it informed me that my password was about to expire so I changed it. I then could not login in to applications on my original machine that used Trusted Logins until I logged out and logged back in.
Steve
October 21, 2009 at 12:37 am
The Trick is to create the same username with same password in SQL Server Locally and the Web servers locally.
very good.
tested.
Roy Ernest (9/1/2009)
mforbes (7/30/2009)
If I understand you right, you said the database server is not part of the domain. If that is the case the you can not use windows Authenication. You would need to use sql Authenication but the problem with that is you said SQL is not set to run mixed mode which it would need to be. The statement you made about the only accounts that can be added are local windows accounts is not an acurate statement. When these local accounts are added to SQL they are assentialy SQL accounts. In your connection string you should not be running it as trusted connection. You would need to speacify the account name and password in the connection string. The draw back to that would be it is passed in plain text over the wire unless you are using SSL.Are you sure about this? If I am not mistaken, this is how SQL Server and Webservers in DMZ are set up with Windows Authentication. The Trick is to create the same username with same password in SQL Server Locally and the Web servers locally. You create a Login for the Local windows account and then use Integrated security in your IIS with the application pool running under the Account created in the web server. You do not need to specify the Password in the Config file at all. This will work with the same Token method as a normal Windows Authentication.
Just my 2 cents
:w00t:
October 21, 2009 at 1:00 am
paste some other way:
create some shortcuts…
C:\Windows\System32\runas.exe /netonly /user:domain\username “C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe”
Replace “domain\username” with your info. So if your domain is mycompany then it would be mycompany\steve.novoselac for example.
What happens is that then when you run those apps from those shortcuts it will prompt you for your domain password, you put it in, and it runs the app in the context of your domain user. You can then change the icon for each of these pretty easy, just browse to the exe in the second part when clicking the change icon button on the shortcut properties (the shortcuts are actually links to runas.exe which is a generic icon)
In Vista, for instance, if you are testing SQL (SSMS), you might get this error:
Login failed for user ''. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
The shortcuts above will get you around it in the situation where your computer is not not on the domain or you are not logged in as a domain user..
These tricks above are especially good if you need to connect to SSAS (Analysis Services) since it is only Windows Authentication. And also, the IT department doesn’t really need to have consultant machines on the domain, or VM’s set up, etc, instead they can use these workarounds
February 25, 2010 at 4:00 am
This also happens when trust is beaked in a domain forest, you login with a user from another domain and it cannot be trusted: Gives the same error
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply