Login failed for user (null)

[Bob Segrest]

Hello Everyone,

I am fairly new to the .NET environment and have read all of the previous posts
related to this topic.

I am running Visual Studio .Net on a Windows XP workstation.

My VB.Net application uses a SqlDataAdapter and SqlConnection created by the
Data Adapter Configuration wizard.

I am logged into my workstation using a windows domain account with domain
administrator priveledges.

My database server is in the same domain.

When I created my connection in the Data Adapter Configuration wizard, I pushed
the Test Connection button and it connected without any problem. If I expand
the connection in the Visual Studio Server Explorer, I can see all of the
tables in the database without difficulty.

The SQL connect String generated by the wizard is:
"workstation id=BOBSLAP;packet size=4096;integrated security=SSPI;data
source=PROJECTDEMO;persist security info=False;initial
catalog=MPSSampleDatabase"

So, why am I getting a "Login failed for user '(null)'. Reason: Not associated
with a trusted SQL Server connection." error?

Help!!!

 

[William \(Bill\) Vaughn]

SSPI security assumes that the user credentials are known to the SQL Server.
Setup a login account matching your credentials and give the account rights
to the database you intend to access.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Bob Segrest]

Hello William,

Thank you for your response!

Two points...

1. As the account I am using is a domain administrator, it is a member of
the
local Administrators group which maps into the BUILTIN/Administrators
SQL login which has database owner priveledges for the entire database
by default.

2. Having read this in an earlier post I explicitly created a user login on
the
SQL server for my domain account. I set the default for this login to
my
target database and granted public, dbreader and dbwriter access. I
subsequently granted owner priveledge to no avail.

Did I miss something?

I have managed to get my application to run using a SQL login. However as
this means passing an unencrypted password, it not a real world option.

I'm still stuck, but haven't given up. Any help would be greatly appreciated.

Bob

 

[Paul Clement]

¤ Hello William,
¤
¤ Thank you for your response!
¤
¤ Two points...
¤
¤ 1. As the account I am using is a domain administrator, it is a member of
¤ the
¤ local Administrators group which maps into the BUILTIN/Administrators
¤ SQL login which has database owner priveledges for the entire database
¤ by default.
¤
¤ 2. Having read this in an earlier post I explicitly created a user login on
¤ the
¤ SQL server for my domain account. I set the default for this login to
¤ my
¤ target database and granted public, dbreader and dbwriter access. I
¤ subsequently granted owner priveledge to no avail.
¤
¤ Did I miss something?
¤
¤ I have managed to get my application to run using a SQL login. However as
¤ this means passing an unencrypted password, it not a real world option.
¤
¤ I'm still stuck, but haven't given up. Any help would be greatly appreciated.
¤
¤ Bob
¤

Just to verify, is this a VB.NET or ASP.NET app. The error message you've identified indicates that
the authenticated user credentials are not being delegated to the remote SQL Server machine, which
is symptomatic of an ASP.NET application configuration problem.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Bob Segrest]

Hello Paul,

Thank you for your response!

I am using VB to create an ASP.Net web application rather than a
traditional windows application. I appologize for failing to make
this clear.

Your question leads me to suspect I have missed something...

How do I solve my problem?

Bob

 

[William \(Bill\) Vaughn]

ASP.NET web applications don't log on with the credentials of the user (not
usually). They log on with the ASPNET account which (as with a user account)
have to be installed on SQL Server. Add a login for the ASPNET user and you
should be fine.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Bob Segrest]

Hello again Bill!

Ok... But the ASPNET account is, by default at any rate, a local system account
and this means ASPNET on my development workstation isn't the same as
ASPNET on the sql server I am trying to access.

I don't have any problem creating a domain account with the username
ASPNET. But what, if anything, do I need to do to get both my workstation
and my sql server systems to use it?

We are getting closer.....

Bob

 

[Paul Clement]

¤ Hello Paul,
¤
¤ Thank you for your response!
¤
¤ I am using VB to create an ASP.Net web application rather than a
¤ traditional windows application. I appologize for failing to make
¤ this clear.
¤
¤ Your question leads me to suspect I have missed something...
¤
¤ How do I solve my problem?
¤

If you're not using impersonation then go with Bill's suggestion. When impersonation is disabled
(the default) the identity under which the thread executes is ASPNET (or NetworkService under
Windows 2003). Just keep in mind that ASPNET is a local account so you will need an identical
account on the SQL Server box with the exact credentials for delegation to succeed.

If you want to impersonate the authenticated user through your ASP.NET app then you will need to
enable impersonation. This is the first step.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsent7/html/vxconimpersonation.asp

The second step involves setting the appropriate security for your web app. If you have Anonymous
authentication enabled the impersonated identity is either the IUSR or IWAM account. Probably not
what you want. If you enable Basic authentication then you can impersonate and delegate the (clear
text) credentials of the authenticated user (via IIS) to a remote resource (such as SQL Server).

If you configure your web app for Integrated Windows Security you *cannot* delegate credentials (via
IIS) to a remote resource without enabling Kerberos. This is because NTLM performs the
authentication (by default) and IIS never actually receives the encrypted credentials.

The above scenario is what typically stop folks cold, because Integrated Windows security will
appear to work on their development machine, but only because they are logged on locally, but will
fail once they move the ASP.NET app to their production web server.


Paul
~~~~
Microsoft MVP (Visual Basic)