Trying to get a VB.net sample working with SQL Server.

[John Blair]

Hi,

I am trying to get the datacache1 sample working on my PC.
I have downloaded the MSDE 2000 sample SQL Server database and the pubs
sample database and installed it.
I can view the contents of the pubs database.
However, i have trouble connecting to this database from VB.Net

The connection string is:

MyConnection = New
SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")

When i run the sample i get the following error (see below):
This is not surprising as there is no ASPNET user account setup for pubs.

So my question is: How do i configure the pubs database with the correct
user accounts and passwords for the samples to work?

Any help much appreciated.

Also im wondering does microsoft publish a url for telling us how to setup
SQL server for usage with VB.net?

Thanks a lot....John.


Server Error in '/datacache1' Application.
--------------------------------------------------------------------------------

Login failed for user 'WINXP1\ASPNET'.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user
'WINXP1\ASPNET'.

Source Error:

Line 26:
Line 27: Dim ds As New DataSet
Line 28: myCommand.Fill(ds, "Authors")
Line 29:
Line 30: Source = New DataView(ds.Tables("Authors"))

Source File: c:\inetpub\wwwroot\datacache1\WebForm1.aspx Line: 28

 

[Chris, Master of All Things Insignificant]

Two choices as I see it.

1: add the ASPNET user account to access your database.
2: supply the user and password parameters in your connection string to give
access to a user setup in the database.

Chris

 

[Greg Burns]

So my question is: How do i configure the pubs database with the correct
user accounts and passwords for the samples to work?

It is pretty easy if you have SQL Enterprise Manager. If not, then not so
much. :^)

Quick & dirty way: If this is purely for testing on your home box, then just
add the ASPNET user to the local adminsitrator group in Users & Computers.
That will make that user admin for all the databases on your MSDE.

If your have SQL EM that are more appropriate ways of setting this up
correctly. You can even do it using a command prompt and OSQL if you are
feeling so inclined.

HTH,
Greg

 

[John Blair]

Very impressed Greg thanks - that worked a treat!

I had the ASPNET user on my system in the "users" group but not in the
administrators group.

Could you clarify one thing for me....i normally work with oracle ...this is
the first time with SQL Server ....i was thinking
the database connection trying to use the ASPNET user was a DATABASE user
and not a windows XP user account.....which database user account
is used for the SQL Server connection and how does VB.net know the correct
password?

Thanks a lot!

 

[Greg Burns]

SQL server can have two types of logins. SQL accounts and Windows accounts.
You can setup SQL to run in mixed mode (SQL and Windows) or Windows only.
(I think MSDE is Windows only by default??)

ASPNET is a windows account.. One correct method of setting up of SQL (if
you have the GUI tools, which MSDE does not come with), is to add the
windows ASPNET user to the server (this allows it to connect to the server,
but with no access to any databases). Next step is to map that windows
account to a SQL login and give it approriate permissions to a database.

As far as your question...

When a ASP.NET page executes, it uses the ASPNET windows identity. (You can
change this in web.config with <identity impersonate="true"/>, but I
wouldn't unless you have a good reason to.). In your connection string you
told it "Trusted_Connection=yes" (Note: I've normally seen
"Trusted_Coonection=True", or "Integrated Security=SSPI", which both are
equivalent. I've NEVER seen Trusted_Connection=yes", but maybe that works
too!?!) That says to connect to the database using the identity of the
current user (which going to be the ASPNET user, since this is a web app).
No need to specify a password, because it is going to use windows
authentication, not SQL authentication.

Make any sense? I am beginning to ramble.

Greg

 

[John Blair]

Greg,

Thanks very much for taking time to reply....SQL Server logon approach is
much clearer thanks!

FYI " Trusted_Connection=yes" was taken directly from the online sample
(url below) and works on my win XP PC:

http://samples.gotdotnet.com/quicks...ickstart/aspplus/samples/cache/datacache1.src

 

[Greg Burns]

A quick search on google and I see Trusted_Connection=yes is pretty commonly
used. One of my ADO.NET books talks about all the these different valid
variations. I'll have to look it up later tonight.

Greg

 

[Greg Burns]

A quick search on google and I see Trusted_Connection=yes is pretty
commonly used. One of my ADO.NET books talks about all the these different
valid variations. I'll have to look it up later tonight.

Greg

From "ADO.NET Example of Best Practices for C# Programmers" (p.50)

Note: When setting Boolean properties (in connection string), you can use
'yes' instead of 'true', and 'no' instead of 'false'.

That clears that up.

Greg