Can't Get SQL Authentication to work With XP Pro SP2 in very simple test case

I'm trying to work my way through the steps of using a User Id and Password in a connection string.

I'm working with SQL 2005 Express, VS2005, in the development server. Got an error I can't get around...tried it several diffent ways on a slightly more complicated test site...no joy...so went to the MSDN tutorial...made the most "vanilla" test I could think of, and still can't figure it out.

I thought it would be simple enough that I could post the whole thing (below)

The test works fine with Integrated Security = True in the connection string. When I remove that phrase, I get the error:

{"CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file E:\MyPath\App_Data\VSST_DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."}

This occurs on the cn.Open statement below.

It gets past the login, so I know that the SQL User and password match up correctly.

==========================
<connectionStrings>
<add name="VSST_CN"
connectionString="Data Source=MyServer\SQLEXPRESS;AttachDbFilename=E:\MyPath\App_Data\VSST_DB.mdf;User Id = VSST; Password=vsst123"
providerName="System.Data.SqlClient"/>
</connectionStrings>

=========================
Page Code Behind (no controls on page)

Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("VSST_CN").ToString())

Dim cmd As SqlCommand = New SqlCommand("SELECT COUNT(*) FROM VSST_Table", cn)
cn.Open()
Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
rdr.Read()
Response.Write(rdr(0).ToString())

End Sub
End Class

=================
The DB

Table VSST_Table,
ID is int, primary key, identity
Field1, Field2, Field3, Field4, Field5 are varchar(50)

I added one record ("A", "B", "C", "D", "E") to the table through VS2005 Server Explorer

This shows up in Count = 1 from running the page when Integrated Security = True

=================
In SSMSE: (this is ALL I did, tried to use the minimum so not to confuse...)

I added the SQL Authentication Server level user "VSST" with the password "vsst123" (and the login works, as noted above)

I attach the .mdf

I add VSST to the Database Users, and give it db_owner

I add VSST to the Table with all permissions checked.

=====================================

I can't figure this out. This is a very vanilla test and I'm stumped. I'm about to give up on SQL Authentication entirely (at least for now), and just try to filter my inputs for SQL Injections...that's the only reason I have (at this stage in my biz plan) for needing SQL Authentication. On the other hand, I really don't like being this stumped on something that is so widely promoted as a common practice.

Any help on this would be greatly appreciated.

Thanks!

It's an XP SP2 security issue. The connect is running within an application sandbox which is not granted the authority to attach, create, or restore a database. The integrated security scenario works, because the Windows credentials are passed through. We spent days trying to figure a way around this one when dealing with a click once application installation and finally gave up. There wasn't a single permutation that we could use to get around the security restrictions that XP SP2 put in place. If the database already existed, everything connected and worked just fine. But, it simply refused to attach a database.|||

Thanks for the input. Wow. Good to know I wasn't completely blowing it....it looked too easy.

So, maybe it will work on the Windows 2003 VPS Server I'm renting...I guess I'll have to give it a try. But maybe it tells me I need to consider why SQL Authentication is critical to this app.

Grazie!

|||

I think there was something in the decision tree that I missed...

As I am gradually putting this together, it looks like the first choice is "1 server => windows authentication" vs "multiple servers (plus some other cases) => sql authentication"

After that, everything that is written about sql authentication seems to assume a level of professional expertise which is appropriately associated with a multi-server environment. Which probably means that the developers may have XP on their desks, but that they're logging into an W2003 development environment...so they never get exposed to this problem (or are aware of it and bypass it).

If you miss the first branch in the decision tree, this is never mentioned...it's all about encrypting web.config and guarding against sql injection by limiting table access, clever ways to use trusted this and that....but never "maybe you should just use Windows Authentication, if it's a single server app."

Long way to the first u-turn....hazards of being a newbit!

|||

Similar grief here

http://www.mcse.ms/message2347651.html