?
Solved

MSSQL 2008r2 Express User Instance DB creation and access

Posted on 2011-09-04
3
Medium Priority
?
355 Views
Last Modified: 2012-08-13
My app has been working fine with using MSSQL 2008r2  through an IP address.  However, I'm getting to a point where I want to distribute the application and from what I'm reading using a user instance will help me get around having the end user manually set TCPIP settings in MSSQL2008r2 express.

I'm able to get the DB to create using:
            szQuery = @"CREATE DATABASE MyDB " +
                      "ON PRIMARY (NAME=MyDB_Data, " +
                      "FILENAME = '" + szDBPath + "')";

            try
            {
                using (SqlConnection aConnection = new
                    SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=master;Integrated Security=SSPI;User Instance=true;"))// ;AttachDbFilename=" + szDBPath )) 
                {
                    aConnection.Open();
                    using (var dbCommand = aConnection.CreateCommand())
                    {
                        dbCommand.CommandText = szQuery;
                        dbCommand.ExecuteNonQuery();
                    }
                }
            }

Open in new window


But then when I try to access the DB I get a login failed for the same user that created it:

            try
            {
                using (SqlConnection aConnection = new 
                    SqlConnection(@"server=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=MyDB;User Instance=true;")
                {
                    aConnection.Open();
                    using (SqlCommand dbCommand = new SqlCommand(szQuery, aConnection))
                    {
                        dbCommand.ExecuteNonQuery();
                    }
                }
            }

Open in new window


Failes with: "Cannot open database "MyDB" requested by the login. The login failed.
Login failed for user 'MyAD\sj'."

I don't just want to fix the issue; I want to understand what's going on...

Once this happens the files located in user\sj\...\sqlexpress\*.* are held open and even shutting down the sqlexpress service doesn't seem to help.   The only way I can remove the DB and retry is to restart the computer then delete the files.

Can I access the DB from studio express?  The DB doesn't show when I open it.

Anyone have any insight? Thanks.
0
Comment
Question by:sej69
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Expert Comment

by:dave4dl
ID: 36480741
I see a lot of things that you dont do in your database creation command that are normally done (like creating a log for the database).  From the sounds of it, the database creation is not complete so it just hangs waiting for the rest of the database creation commands it needs.  Attached is an example creation script.
USE [master]
GO

/****** Object:  Database [MyDB]    Script Date: 09/04/2011 09:23:45 ******/
CREATE DATABASE [MyDB] ON  PRIMARY 
( NAME = N'MyDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLSERVER2008\MSSQL\DATA\MyDB.mdf' , SIZE = 8741888KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MyDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLSERVER2008\MSSQL\DATA\MyDB_log.ldf' , SIZE = 9027840KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [MyDB] SET COMPATIBILITY_LEVEL = 90
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MyDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [MyDB] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [MyDB] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [MyDB] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [MyDB] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [MyDB] SET ARITHABORT OFF 
GO

ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [MyDB] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [MyDB] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [MyDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [MyDB] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [MyDB] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [MyDB] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [MyDB] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [MyDB] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [MyDB] SET  DISABLE_BROKER 
GO

ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [MyDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [MyDB] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [MyDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [MyDB] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [MyDB] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [MyDB] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [MyDB] SET  READ_WRITE 
GO

ALTER DATABASE [MyDB] SET RECOVERY FULL 
GO

ALTER DATABASE [MyDB] SET  MULTI_USER 
GO

ALTER DATABASE [MyDB] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [MyDB] SET DB_CHAINING OFF 
GO

Open in new window

0
 

Accepted Solution

by:
sej69 earned 0 total points
ID: 37204233
Can't use integrated_security in a user instance...  Problem was resolved.
0
 

Author Closing Comment

by:sej69
ID: 37230545
I solved the issue.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question