can't connect to SQLEXPRESS db via ASP.NET

I created a database in SQLExpress that I can connect to fine from C# console and windows forms apps.

However, when I try and open a connection using ASP.NET, the connection fails with:

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

It seems that ASP.net webapps will run under a user called <machine name>\ASPNET, but my SQLExpress db doesn#t know about this user.

How can I add this user to SQLExpress, or is there another way around this ?
zorba111Asked:
Who is Participating?
 
RiteshShahCommented:
since you don't have client tool, you have to do it from DOS (cmd) prompt.

http://ayende.com/Blog/archive/2005/11/02/SQLExpress2005.aspx


0
 
RiteshShahCommented:
well there is a way to add ASPNET user in your sqlexpress but I guess you should use any of the SQL User specifically in SQL connection string. Can you show your connection string to me?
0
 
RiteshShahCommented:
basically what happens is, when you run your C# windows application, it tries to connect as local user to SQLExp. which is trusted there but when you try to use same connection string form ASP.NET, it tries to connect as \ASPNET which is totally unknown from SQLExp.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
RiteshShahCommented:
run below script in your SQL Express to add ASPNET user for your database,



EXEC sp_grantlogin 'KINKLADZE\ASPNET'

USE (YOUR DATABASE NAME)

EXEC sp_grantdbaccess 'KINKLADZE\ASPNET'

EXEC sp_addrolemember 'db_owner', 'KINKLADZE\ASPNET'
0
 
zorba111Author Commented:
here is the connection string that worked fine for console or winform app:

"Data Source=(local)\\SQLEXPRESS;Initial Catalog=AutoLot;Integrated Security=True;Pooling=False"
0
 
zorba111Author Commented:
EXEC sp_grantlogin 'KINKLADZE\ASPNET'
USE (YOUR DATABASE NAME)
EXEC sp_grantdbaccess 'KINKLADZE\ASPNET'
EXEC sp_addrolemember 'db_owner', 'KINKLADZE\ASPNET'

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Great, but where do I get the SQL prompt to key this in ?
My SQLExpress has installed some icons in the Start menu:

Microsoft SQL Server 2005
|
Configuration Tools
|
SQLServer Configuration Manager
SQLServer Error and Usage Reporting
SQLServer Surface Area Configuration

I've looked in all of these and can't find a prompt
0
 
RiteshShahCommented:
well in that case, you have to add ASPNET user and grant permission for your DB as I described in my previous post.
0
 
RiteshShahCommented:
are you accessing your database through server explorer in visual studio?
0
 
zorba111Author Commented:
>> are you accessing your database through server explorer in visual studio?

i was, while accessing from console / winform app
0
 
zorba111Author Commented:
ok, got it sorted by mixing and matching from your script above and from Ayende Rahien's page you referred me to.

For the record, here is what it took.

doubly good, because now I know where the sqlexpress version of sql command prompt is and how to access i!
C:\>osql -S .\SQLExpress -E
1> s
2> sp_grantlogin '.\ASPNet'
3> go
Msg 102, Level 15, State 1, Server KINKLADZE\SQLEXPRESS, Line 2
Incorrect syntax near '.\ASPNet'.
1> sp_grantlogin `.\ASPNet`
2> go
Msg 102, Level 15, State 1, Server KINKLADZE\SQLEXPRESS, Line 1
Incorrect syntax near '`'.
1> sp_grantlogin 'KINKLADZE\ASPNet'
2> go
1> uses AutoLot
2> go
Msg 2812, Level 16, State 62, Server KINKLADZE\SQLEXPRESS, Line 1
Could not find stored procedure 'uses'.
1> use AutoLot
2> go
1> sp_grantdbaccess 'KINKLADZE\ASPNet'
2> go
1>
2> sp_addrolemember 'db_owner', 'KINKLADZE\ASPNET'
3> go

Open in new window

0
 
zorba111Author Commented:
thanks !
0
 
RiteshShahCommented:
you are welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.