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

x
?
Solved

MSSQL 2008r2 Express User Instance DB creation and access

Posted on 2011-09-04
3
Medium Priority
?
364 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
  • 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

572 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