Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1120
  • Last Modified:

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 ?
0
zorba111
Asked:
zorba111
  • 7
  • 5
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now