Visual Studio 2005 Login Components and ASPNETDB Options

[greg.hart]

Hello all,

Just wanted to get some clarification on something. I was setting
out
to create a login page for people to login and then be able to access
certain aspects of the website. Instead of doing what I usually do
which is store the username password in a db table then compare what
the user enters from the text boxes to what's in the db, I thought I
would try the login components from the toolbox.

In looking at how to wire those up it seems I have come across two
similiar ways, but they are a different and I am not sure if one is
better than the other etc.

In on item I found it was suggested to use the aspnet_regsql to
create
the aspnetdb and the associated tables in my sql instance, add users,
which I did with a sql query I found, then wire it to the login
component.

The other item I found was within Visual Studio, click the ASP.NET
Configuration icon from within my project, which in turn creates an
aspnetdb.,df in my App_Data directory and go from there.

Not sure if the latter option is used when you don't have SQL server
available to you, but it seemed much easier to get wired up and
worked
right away, versus the former option which I had trouble getting
wired
up.

Thoughts?
Greg

 

[Norman Yuan]

The two ways you described, well, if you study a bit more on SQL membership
provider used for ASP.NET project/website, are the same way: you need to
have an instance of SQL Server (Express) available.

 

[Steve C. Orr [MCSD, MVP, CSM, ASP Insider]]

The first technique you mentioned (regsql) is more professional and should
be used for complex sites that need to be highly scalable.

The second technique you mentioned uses SQL Express by default. This is
fine for smaller, relatively informal web sites.

--
I hope this helps,
Steve C. Orr,
MCSD, MVP, CSM, ASPInsider
http://SteveOrr.net
http://iPhonePlaza.net

 

[clintonG]

Be advised some (perhaps many) hosting services are refusing to support SQL
Express databases.

 

[greg.hart]

Be advised some (perhaps many) hosting services are refusing to support SQL
Express databases.












- Show quoted text -

Ok. I'm back. I started down the path of just using SQL express in
my local dev environment, just to see how this all works. Everything
seems to work great so now I am looking to move it to my prod box
which is a full blown SQL 2005 instance. Of course I was hoping I
would just be able to copy my APP_DATA directory over and everythign
would still work without issue. Doesn't seem to be the case. I have
seen a number of postings and it appears people are doing different
things to get it working. I have tried to attach the DB in my SQL
instance but my local version doesn't seem to use anything special in
the web.config to know it is there so I am not sure how to make it
aware of the DB.

I keep getting:

An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified)

Thanks,
Greg

 

[Juan T. Llibre]

re:
!> I was hoping I would just be able to copy my APP_DATA
!> directory over and everything would still work

You can't simply attach a SQL Server Express database file to SQL Server 2005.

What I would do is export your SQL Server Express database
to a .sql file, create a new database with the same name in
the SQL Server 2005 server, and import your .sql file into it.

You must make sure that you include both the schema
and the data in your exported .sql file.

re:
!> my local version doesn't seem to use anything special in the web.config to know it is there

There should be a connection string there, somewhere.

re:
!> so I am not sure how to make it aware of the DB.

Look at the differences in connection string syntax
for SQL Server 2005 and SQL Server Express, here :

http://www.connectionstrings.com/?carrier=sqlserver2005

Make sure you modify your current connection string so it conforms to SQL Server 2005 syntax.
You can't just copy the connection string from Express to full SQL and expect it to work.

re:
!> An error has occurred while establishing a connection to the server.
!> When connecting to SQL Server 2005, this failure may be caused by the
!> fact that under the default settings SQL Server does not allow remote
!> connections. (provider: SQL Network Interfaces, error: 26 - Error
!> Locating Server/Instance Specified)

That sounds like the instance name was not changed in the connection string,
but may have one of a number of causes. Here are some :

1) Make sure your server name is correct, e.g., no typo on the name.

2) Make sure your instance name is correct and that the instance actually exists on your target machine.

3) Make sure the server machine is reachable,
i.e., that the DNS can be resolved correctly, and that you are able to ping the server.

4) Make sure that the SQL Browser service is running on the server.

5) If a firewall is enabled on the server, you need to put
sqlbrowser.exe and/or UDP port 1434 into a firewall exception.

Also, you might have to communicate via IP, instead of the SQL Server name,
if the SQL Server is not within the same local network as the web server.

Be advised some (perhaps many) hosting services are refusing to support SQL
Express databases.












- Show quoted text -

Ok. I'm back. I started down the path of just using SQL express in
my local dev environment, just to see how this all works. Everything
seems to work great so now I am looking to move it to my prod box
which is a full blown SQL 2005 instance. Of course I was hoping I
would just be able to copy my APP_DATA directory over and everythign
would still work without issue. Doesn't seem to be the case. I have
seen a number of postings and it appears people are doing different
things to get it working. I have tried to attach the DB in my SQL
instance but my local version doesn't seem to use anything special in
the web.config to know it is there so I am not sure how to make it
aware of the DB.

I keep getting:

An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified)

Thanks,
Greg

 

[xman]

re:
!> I was hoping I would just be able to copy my APP_DATA
!> directory over and everything would still work

You can't simply attach aSQLServerExpress database file toSQLServer2005.

What I would do is export yourSQLServerExpress database
to a .sqlfile, create a new database with the same name in
theSQLServer2005server, and import your .sqlfile into it.

You must make sure that you include both the schema
and the data in your exported .sqlfile.

re:
!> my local version doesn't seem to use anything special in the web.config to know it is there

There should be a connection string there, somewhere.

re:
!> so I am not sure how to make it aware of the DB.

Look at the differences in connection string syntax
forSQLServer2005 andSQLServerExpress, here :

http://www.connectionstrings.com/?carrier=sqlserver2005

Make sure you modify your current connection string so it conforms toSQLServer2005 syntax.
You can't just copy the connection string from Express to fullSQLand expect it to work.

re:
!> An error has occurred while establishing a connection to theserver.
!> When connecting toSQLServer2005, this failure may be caused by the
!> fact that under the default settingsSQLServerdoes not allow remote
!> connections. (provider:SQLNetwork Interfaces, error: 26 - Error
!> LocatingServer/Instance Specified)

That sounds like the instance name was not changed in the connection string,
but may have one of a number of causes. Here are some :

1) Make sure yourservername is correct, e.g., no typo on the name.

2) Make sure your instance name is correct and that the instance actually exists on your target machine.

3) Make sure theservermachine is reachable,
i.e., that the DNS can be resolved correctly, and that you are able to ping theserver.

4) Make sure that theSQLBrowser service is running on theserver.

5) If a firewall is enabled on theserver, you need to put
sqlbrowser.exe and/or UDP port 1434 into a firewall exception.

Also, you might have to communicate via IP, instead of theSQLServername,
if theSQLServeris not within the same local network as the webserver.

Juan T. Llibre, asp.net MVP
asp.net faq :http://asp.net.do/faq/
foros de asp.net, en español :http://asp.net.do/foros/





Ok.  I'm back.  I started down the path of just usingSQLexpress in
my local dev environment, just to see how this all works.  Everything
seems to work great so now I am looking to move it to my prod box
which is a full blownSQL2005 instance.  Of course I was hoping I
would just be able to copy my APP_DATA directory over and everythign
would still work without issue.  Doesn't seem to be the case.  I have
seen a number of postings and it appears people are doing different
things to get it working.  I have tried to attach the DB in mySQL
instance but my local version doesn't seem to use anything special in
the web.config to know it is there so I am not sure how to make it
aware of the DB.

I keep getting:

An error has occurred while establishing a connection to theserver.
When connecting toSQLServer2005, this failure may be caused by the
fact that under the default settingsSQLServerdoes not allow remote
connections. (provider:SQLNetwork Interfaces, error: 26 - Error
LocatingServer/Instance Specified)

Thanks,
Greg- Hide quoted text -

- Show quoted text -

Hi Greg,

in case you have trouble moving the SQL Server Express db to the
target server using export / import one other approach is to use xSQL
Software's (http://www.xsqlsoftware.com) schema and data compare tools
that are free for SQL Server Express and also free for other editions
of SQL Server if the number of objects in the database does not exceed
certain limits. The above description refers to the "after the two
week trial" since during the trial you get them with no restrictions
at all so you can get the job done.

Here are the steps:
1. create a blank database in the target server
2. user xSQL Object to compare and synchronize the schemas of the two
databases (your source and destination) - in essence you are
transfering the schema this way (it should take no more than a couple
of minutes to complete the job)
3. if you want to transfer the data as well use xSQL Data Compare to
compare and synchronize the data in your target database with the
source database - you can include only tables and rows you want (if
you did not want to transfer everything).

Hope this is helpful for you.

JC