ConnectionString For SQL Server 2005?

[Arpan]

I recently installed SQL Server 2005 (Management Studio Express) along
with SQL Server 2005 Express (Configuration Manager) in my Win2K Pro
m/c & use IIS 5.0 to run ASPX projects. I could add my database, named
"MyDB", successfully. I am using the Windows Authentication mode to
login to SQL Server. My server name is "AD\SQLEXPRESS" where "AD" is
the name of my m/c.

I could even establish a connection to the server & the database from
Visual Web Developer 2005 by navigating to "Tools--->"Connect to
Database" menu. The next dialog listed a list of data sources from
which I selected Microsoft SQL Server. In the subsequent dialog titled
"Add Connection", the "Data Source" was already set as "Microsoft SQL
Server (SqlClient)". In the "Server name" textbox, I entered the SQL
Server name i.e "AD/SQLEXPRESS". The "Select or enter a database name"
field listed all the databases from which I chose "MyDB". Next when I
clicked the "Test Connection" button, VWD prompted a message saying
"Test connection succeeded". After clicking the OK button in this
dialog, the "Database Explorer" window pane opened up in VWD which
listed the SQL Server name along with the database name i.e.
"ad\sqlexpress.MyDB.dbo". "MyDB" also listed all the objects like
tables, stored procedures, triggers etc.

The problem I am facing right now is in framing the ConnectionString in
ASPX pages using VWD. The "Database Explorer" window pane shows the
ConnectionString as

Data Source=AD\SQLEXPRESS;Initial Catalog=MyDB;Integrated Security=True

& this is how the ConnectionString looks in the Page_Load sub:

<script runat="server">
Sub Page_Load(ByVal obj As Object, ByVal ea As EventArgs)
Dim dSet As DataSet
Dim sqlConn As SqlConnection
Dim sqlDapter As SqlDataAdapter

sqlConn = New SqlConnection("Data Source=AD\SQLEXPRESS;Initial
Catalog=MyDB;Integrated Security=True")
sqlDapter = New SqlDataAdapter("SELECT * FROM Users", sqlConn)

dSet = New DataSet()
sqlDapter.Fill(dSet, "Users")
........................
........................
........................
End Sub
</script>

But the above code generates the following error:

Cannot open database "MyDB" requested by the login. The login failed.
Login failed for user 'AD\ASPNET'.

Can someone please guide me what am I missing in the ConnectionString?
I believe it's a security/permission issue but just can't figure out
the cause of the error.

My entire work has come to a standstill just because of this problem. I
need help desperately!

Please note that prior to using SQL Server 2005, I was using SQL Server
7.0 & this was the ConnectionString I used to use:

sqlConn = New SqlConnection("Server=(local);Database=MyDB;UID=sa;PWD=")

Using the above ConnectionString, I didn't face any problem while
retrieving data from a SQL Server 7.0 database table.

Thanks,

Arpan.

 

[Patrick.O.Ige]

Arpan have you give the database the right permissions?
Go to Sql Server and give the 'AD\ASPNET' account the right permissions.
Patrick

 

[Arpan]

Thanks, for your prompt response, Patrick.

Go to Sql Server and give the 'AD\ASPNET' account the right permissions

I don't find the 'AD\ASPNET' account in the SQL Server Management
Studio Express IDE. How do I give the 'AD\ASPNET' account the right
permissions?

Thanks once again,

Regards,

Arpan

 

[Arpan]

Patrick, I got the problem resolved. What I did is expanded the DB
named "MyDB" in the SQL Server Management Studio Express, then expanded
"Security" & finally expanded "Users". Right-clicking "Users", I added
a new user. In the dialog that popped-up, I entered 'AD\ASPNET' as the
"Login name", a name in the "User name" field & finally assigned
"db_owner" as the "Database role membership".

That's it!

Regards,

Arpan