Logon Failed connecting to SQL Server

[Guest]

I've gone throught the MSDN walkthrough "Creating a Web Application Using
Visual C# or Visual Basic". When I run the sample I get "Login failed for
user 'dbuser'" at the line with "SqlDataAdapter1.Fill(dSet)". The SQL server
is setup for mixed mode and I have a login in Enterprise Manager for the
dbuser account. I've setup a system DSN in ODBC on the client which tested
succesfully. In Visual Studio 2003 I've chosen "Preview Data..." for the
SqlDataAdapter1, which successfully fills the dataset. I don't understand why
those two methods can access the database, but the aspx page doesn't. I've
triple checked the username and password for each method. Is there something
I've overlooked? I'm attempting this while logged in as the local admin on
the client computer instead of as a domain account, but I wouldn't think this
would make a difference since it's a SQL server login and the other two
methods work under this login. Any help would be appreciated.

 

[Eliyahu Goldin]

Show your connection string.

Eliyahu

 

[recoil]

Your Sql Server is probably set to use Windows Authentication and not
Mixed Mode.
if you are running MSDE then try the following.
MSDE 2000 (without Enterprise Manage)
Start -> Run -> regedit ->
HKLM/Software/Microsoft/MSSQLServer/MSSQLServer/LoginMode
The value will be 1 and needs to be changed to 2.

for SQL Server with Enterprirse Manager
SQL Server 2000 (using Enterprise Manager)
· Open The SQL Server Enterprise Manager
Start menu > Programs > Microsoft SQL Server > Enterprise Manager)
· Expand the tree-control until you can your server
· Right click on the server name and "SQL Server Properties
(Configure)" window will open
· Select the Security tab
· For "Authentication", select "SQL Server and Windows"

 

[Guest]

I'm Using SQL Server 2000, not MSDE and it is setup for mixed mode. If it
wasn't mixed mode, I think the ODBC or "show date" in VS would have failed,
but I could be wrong. The "dbuser" acount was created in Enterprise Manager,
not Active Directory.

 

[Eliyahu Goldin]

So where is the connection string?

Eliyahu

I'm Using SQL Server 2000, not MSDE and it is setup for mixed mode. If it
wasn't mixed mode, I think the ODBC or "show date" in VS would have failed,
but I could be wrong. The "dbuser" acount was created in Enterprise Manager,
not Active Directory.

 

[Guest]

Sorry, but I'm at work now and didn't remember to bring the project in with
me from home (I should have done that). I'll be able to respond again in a
few hours with the info. I didn't have to explicitly create a connection
string (I'm not sure if VS did that in the background). I did the walkthrough
step by step from my local MSDN source, but here is the link to the exact
same one online:

http://msdn.microsoft.com/library/d...ngWebApplicationUsingVisualCOrVisualBasic.asp

 

[Patrick Olurotimi Ige]

David Eliyahu was asking for your connectionstring cos that was the only
way he could see the way u were connecting!
But after going through the step u took from MSDN i guess you didn't go
here to:-

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsintro
7/html/vxwlkusingterminalserverssqlserversonothermachines.asp

Click on the link :-Using SQL Server in Web Applications.
from the page u posted.

Hope that helps
Patrick

 

[Guest]

Actually I had missed that. I changed the IIS settings as it stated (cleared
anonymous, Integrated Windows authentication was checked) and edited the
web.config file (already had <authentication mode="Windows" /> and inserted
<identity impersonate="true" />). The problem persists though. I've restarted
the machine and tried running from the local admin account and a domain admin
account with the same results. Could there be anything else I'm missing. Can
any other settings have an effect, whether IIS, DNS, or anything else? I've
been reading through numerous resources that don't hint to other causes.

 

[Guest]

Also, the only connectionstring I can see in the project is in web.config:

<sessionState
mode="InProc"
stateConnectionString="tcpip=127.0.0.1:42424"
sqlConnectionString="data source=127.0.0.1;Trusted_Connection=yes"
cookieless="false"
timeout="20"
/>

Does this connection string need to point to the SQL Server? I thought it
was for simply storing the session info (which would be stored locally).

 

[Eliyahu Goldin]

No, this connection string has nothing to do with your problem.

You are getting a logon failed message. That is likely because of a problem
with your connection parameters. If you set a breakpoint before Fill
statement and check the connection string, you have a good chance of
spotting the problem straight away.

Eliyahu

 

[Guest]

Sorry, I'm new to ASP programming and datasets. I've set the break at:

SqlDataAdapter1.Fill(dSet)

and looked at the "Locals" watch window. This only provided the info for the
dataset class (dSet). I'm going to have to check the SqlDataAdapter class for
the connection string when I get home from work. I'll post the information at
that time (in about 8 hours).

 

[Guest]

When I stop at the breakpoint for SqlDataAdapter1.Fill(dSet) I show for
SqlDataAdapter1.SelectCommand.Connection.ConnectionString = "workstation
id=DAVID;packet size=4096;user id=dbuser;data source=SERVER2003;persist
security info=False;initial catalog=pubs"

The dbuser is an account setup in Enterprise Manager with read permissions

 

[Guest]

I thought I would also mention that I decided to test connecting to the SQL
Server by writing a simple VB .NET Windows application. I used this as my
connection string:

Conn = New OdbcConnection("Driver={SQL
Server};Server=SERVER2003;Uid=dbuser;pwd=alaska;Database=pubs;")

and used the OdbcDataReader to successfully query the DB and display the
results to a text box.

 

[Guest]

Well, I finally got it to work after more research, but something bothers me
about this. I added this line just before the fill command:

SqlDataAdapter1.SelectCommand.Connection.ConnectionString =
"Server=SERVER2003;Database=pubs;UID=dbuser;PWD=password"

and it now works. But this is completely different from what the Visual
Studio "Configure Data Adapter" wizard creates for a connection string
("workstation id=DAVID;packet size=4096;user id=dbuser;data
source=SERVER2003;persist security info=False;initial catalog=pubs"). I
entered the proper info in the wizard (user name, password, etc), but the
wizard still didn't create anything useful. What am I overlooking?

 

[Eliyahu Goldin]

There is no password in the connection string created by the wizard. That's
why your login failed. I have never used the wizard ard don't know why it
didn't put the password in.

Eliyahu