Showing posts with label login. Show all posts

Thursday, March 29, 2012

Database Configuration Problems

I am trying to learn ASP.NET using Visual Basic Standard Edition 2003 and an MSDE database. I am having problems configuring the login. Is this a workable configuration? Are there instructions somewhere for setting up this configuration? I have no problem using Access 2003, but I would like to use MSDE.Hold everything!!! If you're just starting out, then why not learn ASP.NET 2.0 instead? Go to the Home page of this site and follow the 3 steps of "Getting Started with ASP.NET." You get a great free developement tool (Visual Web Developer Express) and download SQL Express 2005 (which replaces MSDE) too (not sure if this is part of the download... if not, google for download... it's free too). That way, you're up-to-date with the latest technology.

Thursday, March 8, 2012

Database Access Issues

I'm running a simple login application locally with SQLExpress. If I run it straight out of VS2005 it will allow the user to login successfully, however if I run it through IIS it gives me the following error.

Server Error in '/BasicReview' Application.

An attempt to attach an auto-named database for file C:\Documents and Settings\jmfoster\My Documents\Visual Studio 2005\Projects\BasicReview\BasicReview\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

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: An attempt to attach an auto-named database for file C:\Documents and Settings\jmfoster\My Documents\Visual Studio 2005\Projects\BasicReview\BasicReview\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): An attempt to attach an auto-named database for file C:\Documents and Settings\jmfoster\My Documents\Visual Studio 2005\Projects\BasicReview\BasicReview\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832

User instances of SQL Server Express only allow 1 open connection to the database at a time. You'll have to close the connection to the database in VS 2005 before IIS will be able to connect. Open the Data Connections panel, right-click on the database, and click "Disconnect..."

|||

I've closed the connection, closed VS2005 and it still gives me the same error. I'm thinking windows security or sql security is the issue, but I'm not sure.

|||

Out of curiousity, do you have a machine instance (through SQL Express Manager) of a database by the same name?

|||

Is this website in a hosted site, where someone else could have the same SQL Server name?

|||

I installed SQL Express Manager however I was unable to attach the database. For some reason when I try to attach databases the folder view to find it won't allow me to go very deep into the folder tree. However it does show up if in SQL Express Manager if I have VS2005 running and the database attached in there.

I have no other databases on this computer. I just installed everything a couple days ago.

|||

I have a feeling that when you attempted to attach it to the SQL Express manager it may have mucked something up. Try changing the name of the mdb file and update your connection string.

|||

I renamed it but receive the same error.

Let me explain what I'm doing. I'm making a very simple login app. I literally have done very little so far. In VS2005 I went to the 'Project Menu' and clicked 'ASP.NET Configuration' which allowed me to make roles and users. This step created the default database ASPNETDB.MDB in the App_Data folder in the directory of my project. My project is in the default directory for new projects (My Documents\Visual Studio 2005\Projects\BasicReview\BasicReview\App_Data).

I am able to connect to this database within VS2005, and also with SQL Express Manager (only if I have VS2005 attached to the database).

The login is successful within VS2005 ASP.NET Development runtime. However I need to use IIS. I have IIS 6 installed locally and a virtual directory set within the Defaul Website. I can see the Default.aspx page, but once I try to login it fails and displays the above error.

|||

Would someone be willing to list the steps they take to get a login page working on their local machine with IIS and SQL Express? This bump in my road is killing me, I can't understand why something that everyone must do at some point in time would be so difficult.

|||

Since you need to open your site using IIS as opposed to VS2005 web server you will need to attach your database (mdf file) in SQL Express Managment studio. You can do this by opening SQL Exp. Managment, and right clicking on your database and choosing attach.

Then you need to remove your connection string in your web.config and add a new one like

Couple of things to keep in mind about this. By default SQLExpress uses named pipes for client connections. Which will break through IIS connections. You will also need to open up your SQL2005 Configuration Manager and configure TCP/IP for client connections. Also this will break the DB so you can't use it inside of Visual Studio anymore, you'll have to use SQL Express Management Studio to manage your DB.

Database Access Control

VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John BThis is a multi-part message in MIME format.
--030706090505000407000501
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Check out Application Roles
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:
>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>
--030706090505000407000501
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Check out Application Roles (<a
href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp</a>). ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp</a>). 
 
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in
force until the client connection drops out (ie. disconnects). 
 
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader
+ db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table. 
 
HTH 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mailto:mike.hodgson@.mallesons.nospam.com</a>
| W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
MS User wrote:
<blockquote cite="midOxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
</pre>
</blockquote>
</body>
</html>
--030706090505000407000501--|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
--
David Portas
SQL Server MVP
--|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:
> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B