Connection to database problems

Hi,

I am trying to create a program in VS2005 - C# that uses SQL Express as it's database.

Here is the connection string:

@."Data Source=(local)\SQLEXPRESS;Initial Catalog=MyBookings;Integrated Security=True";

Here is the result from the log file:

2007-03-21 10:49:44.36 Logon Login failed for user 'AQUARIUS\Administrator'. [CLIENT: <local machine>]

I could play with this manually and probably get it working BUT this is a solution that is to be installed on computers by users who will have no knowledge of SQL Express.

The database needs to be accessible by the program without user intervention.

How do I go about this or fix the above problem?

Thanks.

hi,

the connection string is correct, so you should check the actual connecting Windows login has been granted connection permissions to the Express instance...

loosely speaking, SQL Server manages security using a so called "2 phases" authentication policy..

the first one is dedicated to connection action, where the provided credentials are validated against the granted logins "list"... granted logins are "principals", both standard SQL Server logins (requiring explicit user name and password credentials) and Windows logins (where only the relative sid is provided as credential as already authenticated by the domain controller/computer they are member of) that have been granted permissions to log in the specified SQL Server instance... for standard logins, like "sa", you have to manage each specific login, where, for Windows logins, you can both manage them at single login granularity as long as at Group granularity..
So, for Windows logins, you can grant connection permissions at "AQUARIUS\AccountName" or, for group management, at "AQUARIUS\GroupName" the specified account is member of...
by default, SQL Server 2005 grants login permissions for integrated security validated connections at LocalAdministrators (BUILTIN\Administrators) and LocalUsers (BUILTIN\Users) members, but you have to manage as required further logins or groups, as long as to a special standard SQL Server login, "sa", if non integrated security (or "mixed security") connections have been enabled as well..
If the provided credentials can not be validated, the connection is refused and the exception you provided is raised...

when the first security phase has been positively validated, SQL Server proceeds in the second one, regarding database access to the specified one... every kind of server level principal but members of the sysadmins server role requires explicit database access permission via a mapping to a database level principal, usually known as a "database user"..

when resolved, the particular login, mapped to a specified database user, can "access" the specified database... then additional security factors comes to play, to permit security constraints at database object level, like table, view, stored procedure, ...., and with more granularity as well, at column level for tables and views database objects, in order to grant permissions according to the required security path, so that only particular database principals can execute the particular stored procedure(s) or select from the particular table(s) or update the particular table(s)...

regards

|||

if your DB is in Local computer use this

SqlConnection myConnection = new SqlConnection("server=(local)\\SQLEXPRESS;Integrated Security=SSPI;database=yourDBname");

if your DB is in App_Data folder use this

SqlConnection myConnection = new SqlConnection("Data Source=.\\SQLEXPRESS; AttachDbFilename=|DataDirectory|\\yourDBname.mdf;Integrated Security=True;User Instance=True");

Thanks
www.sqltree.com

Connection string problem

Hi

I am manually able to create a connection to my db in the vs2005 ide using
Tools->Connect to Database. In the connection I see 'Data
Source=MYSERVER;Initial Catalog=aspnetdb;Integrated Security=True' as the
connection string and 'NET Framework Data Provider for SQL Server' as the
provider. Following these settings in my code and I have used the connection
string;

<add name="Membership" connectionString="Data Source=MYSERVER;Initial
Catalog=aspnetdb;Integrated Security=True"
providerName="System.Data.SqlClient"/
in my app's web.config file. This connection is then used by a custom
membership provider for the create user wizard. Now when I try to create a
new user in the wizard I get the following error;

System.Data.SqlClient.SqlException: Cannot open database "aspnetdb"
requested by the login. The login failed.
Login failed for user 'MYSERVER\ASPNET'.

I was able to work fine when the db was on sql express. What is the problem
and how can I fix it?

Thanks

RegardsJust allow the aspnet user permissions over the db or specify a user. Its because the aspnet user doesnt have permissions to thedb. The connection string is fine. The easiest way is just tospecify a user and give it permission. This way its more secureanyway.|||

The problem is that the asp.net web application is running as an anonymous user, and you haven't given database access to that account (MYSERVER\ASPNET). When you do it in the IDE, it's using your user access right (MYSERVER\YOU).

A bunch of ways to fix it. Run the asp.net app as you is one way (not recommended). Grant access to the myserver\aspnet user is the better approach. Create him a log in to the sql server, grant him access to the database, and give him enough rights to do what you need to do from the web.

|||

How do you grant access myserver\aspnet user?

Thanks

|||It's quite easy.

My instruction is based on SQL Server 2000:

In Enterprise Manager:

1. Right click on the ASPNET account. (I assume you can do this!)
2. Click on the "Database tab"
3. In the database section (immediately below), place a tick in thecheckbox to the left of the database, if you want ASPNET to access thisdatabase.

Exit the dialog boxes.

If you are using stored procedures, you will need to grant EXEC access.

In Enterpise Manager:

1. Right click on the stored procedure.
2. Choose "properties"
3. Choose "permissions"
4. For "public", check the "Exec" column.

Do for all stored procedures.