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

Question

I have created a webservice which is saving some data into to db. But i am getting this error

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

my connection string is
add name="ConnectionString1" connectionString="Data Source=.\SQLExpress;Initial Catalog=IFItest;Integrated Security=True" providerName="System.Data.SqlClient"

Answer 1

Well, the error is pretty clear, no? You are trying to connect to your SQL Server with user "xyz/ASPNET" - that's the account your ASP.NET app is running under.

This account is not allowed to connect to SQL Server - either create a login on SQL Server for that account, or then specify another valid SQL Server account in your connection string.

Can you show us your connection string (by updating your original question)?

UPDATE: ok, you're using integrated Windows authentication --> you need to create a SQL Server login for "xyz\ASPNET" on your SQL Server - or change your connection string to something like

connectionString="Servere=.\SQLExpress;Database=IFItest;User ID=xyz;pwd=top$secret"

if you have a user "xyz" with a password of "top$secret" in your database.

Answer 2

The best option would be to use Windows integrated authentication as it is more secure than sql authentication. Create a new windows user in sql server with necessary permissions and change IIS user in the application pool security settings.

Answer 3

• Either: "xyz\ASPNET" is not a login (in sys.server_principals)
• Or: "xyz\ASPNET" is set up but not mapped to a user in the database test (sys.database_principals)

I'd go for the 2nd option: the error message implies the default database is either not there or no rights in it, rather than not set up as a login.

To test if it's set up as a login

SELECT SUSER_ID('xyz\ASPNET') -- (**not** SUSER_SID)

If NULL

CREATE LOGIN [xyz\ASPNET] FROM WINDOWS

If not NULL

USE test
GO
SELECT USER_ID('xyz\ASPNET')

If NULL

USE test
GO
CREATE USER [xyz\ASPNET] FROM LOGIN [xyz\ASPNET]