SQL Express user permissions?

Hot Topic

OLEDB question
Set Primary Key when normalizing data?
Fatal Error doing simple queries from an sql 2000 to sql 2005 exported database
List of tables in use by a view
Error Message is not Displaying in this Query? why?
SQL Server 2000 and SQL Server 2005 on the same computer
Can't connect to SSIS
What's the difference between "roles" and "users"?
STRANGE SQL Query
Connectivity Book

SQL Server

Send Mail
Log Folder Creation
Problem in SQL 2000 Analysis Service installation on windows 2003
Intercept Result
Importing a SQL File from the Internet...
TFSWarehouseDataSourceView is missing
exposing proprietary data to SSIS (Newbie alert)
x64 Opteron running SQL Server 2005 in 32-bit?
SQL JOB in Express Edition
Notiification Services Tutorial

SQL Express user permissions?

I'm trying to build a web application using SQL2005 Express and Visual Studio Express.  It all works OK on the local machine, but gives an error "Login failed for user xxxx\ASPNET" when used remotely.  I can't find where I can set login permissions for SQLServer 2005 Expresss - can anyone help, please

Thanks

John

Answer this question

SQL Express user permissions?

  • Rockn

    I think I have made some progress, probably narrowing down the problem, but not complete.

    I have built a new app with two databases in App_Data.  One is ASPNETDB, the other is a test database.  I have made xxx\ASPNET the owner of both databases, and mapped the xxx\ASPNET login to both databases, with "user" and "default schema" both "dbo" for both databases.

    I have two grids in my app, one from an ASPNETDB table, one from the test database.  Both display when the app is run from VS and using IIS via IE.

    So far so good.

    BUT ... I have also have a Login control in the app.  I can "log in" when the app runs from VS, but when the app runs in IE I get the error as before:

    Cannot open user default database. Login failed.
    Login failed for user 'xxx\ASPNET'

    SO:  xxx\ASPNET can log into both ASPNETDB and the test database, but the Login control throws the error.

    When this happens there is no related entry in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

    John

  • vbrook

    Hi Laurentiu

    I've issued the CREATE USER for every database in sight, and I've also used ssme to look at the USERS for each database and ASPNET is there, and it seems to have CONNECT permission ...

    As to using some other tool to connect to the database - what do you suggest

    John

  • ForestMax

    I think its very likely that what you describe is the case.

    How do I "verify that there is a user mapped to the ASPNET login" for the datatbase   In SQL2000 I would use Enterprise Manager and add a User.  I've tried adding ASPNET in SQL Server Studio Management Express and giving ASPNET sysadmin role but without success, perhaps because the database in question is embedded in App_Data in the application.

    I can't see anything in the documentation (but it may be there - I just can't find it).

    This should be easy ... its what Visual Web Developer Express is for !!

    If you have any clues how I can give ASPNET the correct permissions I'd be grateful.

    John

  • AkhilaL MSFT

    You can run the following query to determine the database owner based on the master database information; the query assumes the database name is testdb.

    select suser_sname(owner_sid) from sys.databases where name = 'testdb'

    Now change context to the database and verify what is the owner according to the information stored in the database:

    select suser_sname(sid) from sysusers where uid = user_id('dbo')

    I expect that the owner would come out as something else than xxxx\ASPNET and it would be the same for both queries. Let me know if this is not the case.

    Now verify whether the xxxx\ASPNET login is mapped to some user in the database; if it is, the following query should produce a row that lets you know the user name;

    select * from sysusers where sid = suser_sid('xxxx\ASPNET')

    If xxxx\ASPNET is not mapped to a user (the above query did not return a row), you can do the mapping by issuing:

    CREATE USER [xxxx\ASPNET]

    This should allow ASPNET to connect to the database. You can ignore the rest of the message if you issued the CREATE USER.

    If the login was already mapped to a user, you should verify if the user had permission to connect to the database. You can issue the following statement to retrieve the permissions that the user has in the database:

    select * from sys.database_permissions where grantee_principal_id = user_id('user_name')

    There should be a row showing CONNECT for permission_name and GRANT for state_desc.

    If there is no such row, you can grant the permission by issuing:

    grant connect to <user_name>

    This should allow ASPNET to connect.

    Try these and let me know if you managed to give access to ASPNET. If you encounter any error, please include them with your message.

    Thanks
    Laurentiu

  • lololo

    I thought that enabling TCP/IP solved the problem, but it doesn't.  The application tries to connect to the SQL server as user "xxxx\ASPNET" and fails.

    The message at the client is:

    "Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
    Login failed for user 'xxxx\ASPNET'.

    There is no reference in the ERRORLOG file.

    The "login function" I referred to is the Visual Studio Login web control.  I can log in locally, but not remotely.  I presume that this is related in that the membership database is held in the SQL server.

    John

  • ichi

    Have you tried connecting to the database as ASPNET using some other tool than your web application I'd like to know if the connection only fails when it is made by the app or it is always failing. Also, make sure that you issued the CREATE USER for the default database of ASPNET and not for some other database.

    For the last error that you obtained (sqlmgmt.dll), try posting a separate message to SQL Server Tools General.
     
    Thanks
    Laurentiu



  • John Brooking

    I need more information about what you mean by "login function" and what is the error message that you get from SQL Server (please include the message from the SQL Server erorrlog as well).

    Thanks
    Laurentiu

  • xSephirothx

    Hi Laurentiu

    Thank you for your help so far.

    I tried various adjustment to permissions, but no success, so I built an new application (visual web developer express) with a new database (in App_data) with one table.

    I attached the database in in SQL Management Studio Express and followed your suggestion.

    The two first queries you suggested gave the result FRJ-DEVELOPMENT\John as I expected since that's what I'm logged in as.

    FRJ-DEVELOPMENT\ASPNET was not a user, so I issued CREATE USER [FRJ-DEVELOPMENT\ASPNET], then in properties | permissions for the database I observed that FRJ-DEVELOPMENT\ASPNET had "Connect" checked.  I checked "Select" for good measure.

    I have a page in the app with a grid based on the table.  When I run it in VS it runs OK.  When I run it from IE I get:

    Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
    Login failed for user 'FRJ-DEVELOPMENT\ASPNET'.

    [SqlException (0x80131904): Cannot open user default database. Login failed.
    Login failed for user 'FRJ-DEVELOPMENT\ASPNET'.]
       System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +437
       System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
       System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
       System.Data.SqlClient.SqlConnection.Open() +111
       System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
       System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
       System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
       System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
       System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
       System.Web.UI.WebControls.GridView.DataBind() +4
    (etc)

    I've looked at FRJ-DEVELOPMENT\SQLEXPRESS | Properies | Permissions and FRJ-DEVELOPMENT\ASPNET has type Login and has Connect SQL checked (granted by sa).

    When I highlight FRJ-DEVELOPMENT\ASPNET I get the error:

    Could not load file or assembly 'file:///C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlmgmt.dll' or one of its dependencies. The system cannot find the file specified. (mscorlib).  Is that anything related

    This should be easy!  What am I missing

    Thanks
    John

  • PredragB

    By using another tool to connect to the database, I meant using a client like Management Studio or sqlcmd, or writing a small .Net program that attempted to connect to the server as ASPNET.

    Based on the error message that you are receiving, the problem seems to be with how the account is set up in SQL Server, not with some outside setting.

    Try connecting as ASPNET from some other client than your web application and let me know if it fails as well. If it fails, send me the error message from the SQL Server errorlog.

    Thanks
    Laurentiu

  • Hoofy395

    Remote connections are disabled by default. You can use the Surface Area Configuration tool to enable them.

    Thanks
    Laurentiu

  • smozaffari

    ... also ...

    This should be easy and almost automatic - am I missing something   Is there something I should be doing outside SQL to make xxx\ASPNET useable  

    John

  • CoreyMc

    You seem to be getting two errors:

    Error 4064 is "Cannot open user default database. Login failed.".
    Error 18456 is "Login failed for user 'xxxx\ASPNET'."

    This pair of errors is returned when the login has a default database to which it does not have access. 18456 should also appear in the errorlog; you should see an entry like "Error: 18456, Severity: 14, State: 16." followed by the error message.

    Check the default database set for the ASPNET login and ensure that the login has access to it.

    To determine the default database for login ASPNET, you can issue the following query:

    select default_database_name from sys.server_principals where name = 'xxxx\ASPNET'

    Verify that there is a user mapped to the ASPNET login in that database. I suspect there is none and because guest is disabled, ASPNET cannot connect to the database. This leads to the login failure.

    Thanks
    Laurentiu



  • chris t

    Thanks very much for that - it gets me part way, I don't get failure on logging in to the SQLServer.

    BUT ... the Login function fails to authenticate users remotely (it's OK locally).  Any ideas

    Thanks
    John

  • Eric Larson

    This looks like a problem in the login control. The error reported probably has nothing to do with the actual error. The "login failed" error should always be logged to the errorlog file, if it did indeed happen. Probably the control is reporting incorrectly the error. Try contacting the VS team if this is a VS control. From what you have told me, there seems to be no problem with how the login is setup with SQL Server.

    Thanks
    Laurentiu


  • SQL Express user permissions?
Answer this question