problem when connectiong to database

Hi,
I try to make a connection with the database "test" contained in sql server
express 2005.
This is the connection string in the aspx file:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="Provider=SQLOLEDB;Data
Source=laptop\sqlexpress;Integrated Security=SSPI;Initial Catalog=test"
ProviderName="System.Data.OleDb" SelectCommand="SELECT * FROM
[mytable]"></asp:SqlDataSource>
Sql server express is installed with Windows authentification.
When runing the aspx page, i get: "Cannot open database "test" requested by
the login. The login failed"
I suppose the account ASPNET (machine account used for asp.net pages) has
not the right permissions.
So i started my "Sql server Management Studio Express", selected the
database "test" and went to the property "permissions".
There, i tried to add a user by clicking on button ADD: in the following
window, i can introduce the new user, but it refused all
of them (ASPNET, ASP.NET Machine account ...), even my administrator account
"Bart" is not recognized.
So i have two questions:
1)is the error due not enough permissions (of account ASPNET)?
2) if yes, how to give it the right permissions (write)?
Thanks for help
BartBart,
Did you first add the login? I have not used the Express tools, but a
database User is based on a Server Login (either Windows or SQL Server).
Go to the server's Security settings and add a login. Or use CREATE LOGIN.
RLF
"Bart" <baa@.qsd.dfv> wrote in message
news:OA3UE9JbHHA.1296@.TK2MSFTNGP02.phx.gbl...
> Hi,
>
> I try to make a connection with the database "test" contained in sql
> server express 2005.
> This is the connection string in the aspx file:
> <asp:SqlDataSource ID="SqlDataSource1" runat="server"
> ConnectionString="Provider=SQLOLEDB;Data
> Source=laptop\sqlexpress;Integrated Security=SSPI;Initial Catalog=test"
> ProviderName="System.Data.OleDb" SelectCommand="SELECT * FROM
> [mytable]"></asp:SqlDataSource>
> Sql server express is installed with Windows authentification.
> When runing the aspx page, i get: "Cannot open database "test" requested
> by the login. The login failed"
> I suppose the account ASPNET (machine account used for asp.net pages) has
> not the right permissions.
> So i started my "Sql server Management Studio Express", selected the
> database "test" and went to the property "permissions".
> There, i tried to add a user by clicking on button ADD: in the following
> window, i can introduce the new user, but it refused all
> of them (ASPNET, ASP.NET Machine account ...), even my administrator
> account "Bart" is not recognized.
>
> So i have two questions:
> 1)is the error due not enough permissions (of account ASPNET)?
> 2) if yes, how to give it the right permissions (write)?
> Thanks for help
> Bart
>
>|||First, check out the (free) article on connecting on my blog. It discusses a
litany of issues on connecting.
Next, let's take this a step at a time.
a.. If you are building a .NET application as I expect you are, you should b
e using the SqlClient .NET provider--not OLE DB.
b.. If you are specifying SSPI integrated (trusted) security, you need to pr
operly configure your database to accept connections on the ASP accounts as
you seem to be attempting to do. These vary by name based on the version of
IIS you have installed. These are either ASPNET or IUSR_<machinename>.
c.. Consider that an ASP application can connect either through these accoun
ts (Integrated Security=SSPI) or through a named SQL Server login account. I
n the latter case you MUST reconfigure SQL Server Express to accept this typ
e of security. If the former, there is no need to specify a UID or PWD--they
are ignored. Either account must have been granted rights to the database y
ou're trying to open. This is done through the SSMS tools as you have attemp
ted. In SSMSe open a connection to the SQLEXPRESS instance, drill into Login
s and right click on the appropriate account (like ASPNET). Choose propertie
s. Choose User Mapping and check each database this account has rights to ac
cess and the role membership for each.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Bart" <baa@.qsd.dfv> wrote in message news:OA3UE9JbHHA.1296@.TK2MSFTNGP02.phx.gbl...agreen">
> Hi,
>
>
> I try to make a connection with the database "test" contained in sql serve
r
> express 2005.
>
> This is the connection string in the aspx file:
> <asp:SqlDataSource ID="SqlDataSource1" runat="server"
> ConnectionString="Provider=SQLOLEDB;Data
>
> Source=laptop\sqlexpress;Integrated Security=SSPI;Initial Catalog=test"
> ProviderName="System.Data.OleDb" SelectCommand="SELECT * FROM
> [mytable]"></asp:SqlDataSource>
>
> Sql server express is installed with Windows authentification.
>
> When runing the aspx page, i get: "Cannot open database "test" requested b
y
> the login. The login failed"
>
> I suppose the account ASPNET (machine account used for asp.net pages) has
> not the right permissions.
> So i started my "Sql server Management Studio Express", selected the
> database "test" and went to the property "permissions".
> There, i tried to add a user by clicking on button ADD: in the following
> window, i can introduce the new user, but it refused all
>
> of them (ASPNET, ASP.NET Machine account ...), even my administrator accou
nt
> "Bart" is not recognized.
>
>
> So i have two questions:
>
> 1)is the error due not enough permissions (of account ASPNET)?
> 2) if yes, how to give it the right permissions (write)?
>
> Thanks for help
> Bart
>
>
>
>|||Thanks both for replying.
So you both agree that the problem is that account ASPNET must get R/W permi
sions.
Russel:
--
in the Sql server Management Studio Express, i go to Permissions of Server\S
Qlexpress level and there, i see a list like:
AgentSigningCertificate
SQLAuthentificationCertificate
...
Built-in user
Buit-in administrator
Servername\SQLServer2005MSSqluser$server
name$sqlexpress
NT AUTHORITY \ System
public
I can't see account "ASPNET" as i can do when seeing the list in the Active
Directory.
I think it is hidden in NT AUTHORITY \ System.
So i add it and give it "Connect SQL" grant.
Then i select database "test" but there i see only two types of users:
guest
public
and when i grant "connect sql" to Public, i get an error 4627.
William:
--
I work with Visual Web Developer: when making a sqldatasource, i can choose
between:
microsoft SQL Server Database File (SqlClient) but then i 'm requested to ch
oose an MDF file
or
microsoft SQL Server but i must take Provider: for Ole db. If i choose "micr
osoft SQL Server" and Provider for sql server, it switch back to microsoft S
QL Server Database File (SqlClient).
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> schreef in bericht news
:OWjZNxKbHHA.704@.TK2MSFTNGP04.phx.gbl...
First, check out the (free) article on connecting on my blog. It discusses a
litany of issues on connecting.
Next, let's take this a step at a time.
a.. If you are building a .NET application as I expect you are, you should b
e using the SqlClient .NET provider--not OLE DB.
b.. If you are specifying SSPI integrated (trusted) security, you need to pr
operly configure your database to accept connections on the ASP accounts as
you seem to be attempting to do. These vary by name based on the version of
IIS you have installed. These are either ASPNET or IUSR_<machinename>.
c.. Consider that an ASP application can connect either through these accoun
ts (Integrated Security=SSPI) or through a named SQL Server login account. I
n the latter case you MUST reconfigure SQL Server Express to accept this typ
e of security. If the former, there is no need to specify a UID or PWD--they
are ignored. Either account must have been granted rights to the database y
ou're trying to open. This is done through the SSMS tools as you have attemp
ted. In SSMSe open a connection to the SQLEXPRESS instance, drill into Login
s and right click on the appropriate account (like ASPNET). Choose propertie
s. Choose User Mapping and check each database this account has rights to ac
cess and the role membership for each.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Bart" <baa@.qsd.dfv> wrote in message news:OA3UE9JbHHA.1296@.TK2MSFTNGP02.phx.gbl...agreen">
> Hi,
>
>
> I try to make a connection with the database "test" contained in sql serve
r
> express 2005.
>
> This is the connection string in the aspx file:
> <asp:SqlDataSource ID="SqlDataSource1" runat="server"
> ConnectionString="Provider=SQLOLEDB;Data
>
> Source=laptop\sqlexpress;Integrated Security=SSPI;Initial Catalog=test"
> ProviderName="System.Data.OleDb" SelectCommand="SELECT * FROM
> [mytable]"></asp:SqlDataSource>
>
> Sql server express is installed with Windows authentification.
>
> When runing the aspx page, i get: "Cannot open database "test" requested b
y
> the login. The login failed"
>
> I suppose the account ASPNET (machine account used for asp.net pages) has
> not the right permissions.
> So i started my "Sql server Management Studio Express", selected the
> database "test" and went to the property "permissions".
> There, i tried to add a user by clicking on button ADD: in the following
> window, i can introduce the new user, but it refused all
>
> of them (ASPNET, ASP.NET Machine account ...), even my administrator accou
nt
> "Bart" is not recognized.
>
>
> So i have two questions:
>
> 1)is the error due not enough permissions (of account ASPNET)?
> 2) if yes, how to give it the right permissions (write)?
>
> Thanks for help
> Bart
>
>
>
>|||Hi, i found it finally... thanks
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> schreef in bericht news
:OWjZNxKbHHA.704@.TK2MSFTNGP04.phx.gbl...
First, check out the (free) article on connecting on my blog. It discusses a
litany of issues on connecting.
Next, let's take this a step at a time.
a.. If you are building a .NET application as I expect you are, you should b
e using the SqlClient .NET provider--not OLE DB.
b.. If you are specifying SSPI integrated (trusted) security, you need to pr
operly configure your database to accept connections on the ASP accounts as
you seem to be attempting to do. These vary by name based on the version of
IIS you have installed. These are either ASPNET or IUSR_<machinename>.
c.. Consider that an ASP application can connect either through these accoun
ts (Integrated Security=SSPI) or through a named SQL Server login account. I
n the latter case you MUST reconfigure SQL Server Express to accept this typ
e of security. If the former, there is no need to specify a UID or PWD--they
are ignored. Either account must have been granted rights to the database y
ou're trying to open. This is done through the SSMS tools as you have attemp
ted. In SSMSe open a connection to the SQLEXPRESS instance, drill into Login
s and right click on the appropriate account (like ASPNET). Choose propertie
s. Choose User Mapping and check each database this account has rights to ac
cess and the role membership for each.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Bart" <baa@.qsd.dfv> wrote in message news:OA3UE9JbHHA.1296@.TK2MSFTNGP02.phx.gbl...agreen">
> Hi,
>
>
> I try to make a connection with the database "test" contained in sql serve
r
> express 2005.
>
> This is the connection string in the aspx file:
> <asp:SqlDataSource ID="SqlDataSource1" runat="server"
> ConnectionString="Provider=SQLOLEDB;Data
>
> Source=laptop\sqlexpress;Integrated Security=SSPI;Initial Catalog=test"
> ProviderName="System.Data.OleDb" SelectCommand="SELECT * FROM
> [mytable]"></asp:SqlDataSource>
>
> Sql server express is installed with Windows authentification.
>
> When runing the aspx page, i get: "Cannot open database "test" requested b
y
> the login. The login failed"
>
> I suppose the account ASPNET (machine account used for asp.net pages) has
> not the right permissions.
> So i started my "Sql server Management Studio Express", selected the
> database "test" and went to the property "permissions".
> There, i tried to add a user by clicking on button ADD: in the following
> window, i can introduce the new user, but it refused all
>
> of them (ASPNET, ASP.NET Machine account ...), even my administrator accou
nt
> "Bart" is not recognized.
>
>
> So i have two questions:
>
> 1)is the error due not enough permissions (of account ASPNET)?
> 2) if yes, how to give it the right permissions (write)?
>
> Thanks for help
> Bart
>
>
>
>