ASPNET User

[Andrew Chalk]

My application attempts to connect to an SQL Server database as name ASPNET
and Login Name SERVERNAME/ASPNET in response to these commands:
SqlConnection myConnection = new SqlConnection("Data Source=(local);Initial
Catalog=MCSCRE;Integrated Security=SSPI");

myConnection.Open();

However, the user of this database is ASPNET. I can't create a user ASPNET
with a login name SERVERNAME/ASPNET, SQL Enterprise Manager always keeps the
name ASPNET but eliminates the login name. How do I create the correct user
so that my application can log in?

Many thanks.

 

[Juan T. Llibre]

re:

I can't create a user ASPNET with a login name SERVERNAME/ASPNET

You should be able to.

Are you sure that's not SERVERNAME\ASPNET ?
Notice the difference in the slash's direction.

Other than that, which is probably a typo, you could change
the SQL Server's security to "SQL Server and Windows",
instead of "Windows", create a SQL Server login for your database,
and assign it the permissions you want it to have.

You'd have to change the connection string to use either
the loginname/password you created, or a Trusted Connection.

You'll find this to be helpful :
http://support.microsoft.com/default.aspx?kbid=316989

If the SQL Server is in a different machine that the web server, make sure you read this:
http://weblogs.asp.net/AChang/archive/2004/04/15/113866.aspx
(In fact, read that even if your SQL Server is in the same machine as your web server)

 

[Andrew Chalk]

You are right that the '/' is a typo. The basic problem is exactly the one
discussed in the Q316989 that you reference below. Following that article I
changed SQL Server's security to "SQL Server and Windows". I created a new
user of my database and modified my application connection string as
follows:

SqlConnection myConnection = new SqlConnection("Data Source=(local);Initial
Catalog=MCSCRE;User ID=Logger;password=admin");

It still fails with the same error. Even the three line test program in
Q316989 fails in the same way. It is as though I need a user for SQL Server,
and then a second one for the database.

Any idea what is going on here?

Many thanks,

Andrew

 

[Juan T. Llibre]

re:

SqlConnection myConnection = new SqlConnection("Data Source=(local);
Initial Catalog=MCSCRE;User ID=Logger;password=admin");

I've had problems connecting when I use (local) for the SQL Server's name.

Check and see what the real name of your SQL Server instance is,
and use *that* name in your connection.

To check the instance name of your SQL Server, double-click the
SQL Server Service Manager icon in your taskbar, or open the
"Service Manager" app in the "Start Menu", "Programs",
"Microsoft SQL Server" start menu menu group.

The "Server" textbox will tell you the real name for your SQL Server instance.
Try using *that name* in your connection string, instead of (local).

 

[Andrew Chalk]

The latest saga..

Instead of a new user for my database and loging in as him I logged in as
'sa' (the deafult SQL user) from my app. That works!

Is it the case that my ap. was failing to log in to SQL server, rather than
inot my database? Obviously, I am new to SQL Server authentication and it is
proving counter intuitive to me.

Thanks,

Andrew

 

[Juan T. Llibre]

re:

Instead of a new user for my database and loging in as him I logged in as 'sa' (the
default SQL user) from my app. That works!

That explains a lot.
The "sa" user has read/write permissions to *all* SQL Server's databases.

Your problem is *not* the login, but that the user your connection is trying
to *login as* doesn't have permissions to the objects for the database you're
trying to use.

Add the login you want to use to SQL Server and then add that user to your
database's users, and then assign permissions for that database's objects to
your new user.

This is an object-by-object assignment.
Make sure that all the relevant permissions to all needed objects are assigned.

Then, you should have no problem with that new user accessing your db.

 

[Andrew Chalk]

That is exactly right. The ASP.NET runs at a lower privilege level.

I followed your steps and the new user, with restricted rights, is accessing
the database fine.

Now, if only I could find out how the ASP.NET user's rights could be
upgraded so that I can use integrated security.

Many thanks for your help!

Andrew