none
"The query builder failed. Cannot open user default database. Login failed. Login failed for user <User name>"

    Question

  • I'm using Visual Basic Express 2010 and SQL Express 2008 in Windows XP.

    I created a database on my local PC HDD.

    At first I couldn't add a datasource (the .mdf file for the database) because of a "Operating system error 32" which I seemed to have resolved by giving myself full permissions for .mdf file (by right clicking on the file and going into properties and then Security) and restarting the SQL SERVER (SQLEXPRESS) service.

    The dataset for the database is in my solution explorer but when I right click on a table adapter to add a query and attempt to open up the query builder, I get the error in the title above.

    Tuesday, May 08, 2012 7:21 PM

Answers

  • It works now. I just pointed to the .mdf file on my HDD and it just worked when I created my new dataset in VB. Thanks for all your help.

    Tuesday, May 15, 2012 11:38 AM

All replies

  • have you configured your database server for remote connections via named pipes and tcp/ip?

    use the sql configuration manager in your start menu. here are some instructions.

    https://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx?Redirected=true

    Tuesday, May 08, 2012 7:48 PM
  • Thanks for the response. Do I need to configure the database server for remote connections via named pipes and tcp/ip even when my database is on my local machine because the article above says:

    "Thus, if someone simply installs Express and chooses all the defaults, SQL
    Server Express will only be able to have connections originating on the
    local machine where SQL Server is installed." ?

    Tuesday, May 08, 2012 8:23 PM
  • Here's an image of my problem:

    Tuesday, May 08, 2012 8:25 PM
  • I checked now and tcp/ip and named pipes are enabled in the configuration manager.
    Tuesday, May 08, 2012 8:30 PM
  • ok thats good then. so the next step is to configure your database to allow your user. do you have SQL Management studio installed? if not install it from

    https://www.microsoft.com/en-us/download/details.aspx?id=8961

    when you were setting up sqlexpress did you set an SA password, elect for windows authentication, or did you select Mixed mode authentication? if windows/mixed, did you set the Naven_home\Administrator account as a member of the sysadmin role on the server?

    try to login via sql management studio, using either the windows account, or the SA account. let us know how that works for you.

    Tuesday, May 08, 2012 8:50 PM
  • I set up sql express to use Windows authentication. I can start up and use sql server management studio - that's where I created my database. When I start up sql server management studio I get a dialog box titled connect to server with three drop down text boxes:

    server type(database engine), server name(NAVEN_HOME\SQLEXPRESS) and authentication(windows authentication).

    I press enter and I get access.

    Tuesday, May 08, 2012 9:19 PM
  • ok so thats a start. for now your app can use integrated auth.

    now in visual studio, go to View -> Server Explorer -> Data Connections -> RClick -> Add Data Connection. enter in the connection infomation as you did in smss, and the DB shoudl come up in server explorer.

    after that you should be able to drag tables onto your DS designer, or right click and Add a query.

    let us know how it works.

    Tuesday, May 08, 2012 9:45 PM
  • I actually added the database to get a dataset into vb by clicking data>add new data source>database>dataset>new connection>microsoft sql server database file>browsing to the .mdf file on my local hdd>with "Use Windows Authentication" selected in the Log on to the server option>Test Connection (working)>OK.

    That gives me a dataset in vb. Clicking on the dataset in the Solution Explorer gives a diagram. Right clicking on a table adapter and selecting add query eventually gives me the error when I reach the query builder.

    I'm using vb express. Don't think there's a server explorer there. Thanks. If you need any more details just ask.

    Wednesday, May 09, 2012 9:01 AM
  • ok, well based on your error message it appears to be a failure to access the system databases under your user account.

    open ssms back up, login, and go to Security -> Logins, and double click your user account. under General, is there a default database selected at the bottom of the form?

    under Server Roles, make sure your user is a member of the sysadmin group. if thats not the case, check the box, click ok, and try VS again and see if it works now.

    if not, right click your database -> Properties. is your user in the owner field? if not, is the field enabled so you can set it?

    expand your Database in ssms and go to Securty -> Users (note, there is a Security node in the database, and in the server root. logins control connections, whereas Users control teh rights a user has in a specific database. you need both a user and a login). Is your username listed under users?

    if so, double click the user, and look at the login feild. is it filled in with your windows username? look at the schema feild. is it 'dbo'? if not fix both of those. under Database Role Membership, is db_owner selected?

    if your user does not appear under your databases security->user node, double click the user dbo, and look at the login feild. is it your windows username? if not, right click Users -> Add User and set the settings I mention above(username/loginname/default schema/db_owner).

    if for some reason you don't want to make the user DBO, you can instead set their default schema to 'dbo' and give them the db_creator right. this will allow you to create objects under the dbo schema.

    so give that a look-see and let us know where that leaves you.


    • Edited by Endotherm Wednesday, May 09, 2012 8:06 PM
    Wednesday, May 09, 2012 8:05 PM
  • It works now. I just pointed to the .mdf file on my HDD and it just worked when I created my new dataset in VB. Thanks for all your help.

    Tuesday, May 15, 2012 11:38 AM