Configuring SQL Express to actually work...

[JaedenRuiner]

Okay,

I am not used to this much annoyance with things because well, I've never had to administer a database other than with Access, so some of the rules and such are very pesky and annoying.

First off, with the "Add New DataSource" in VB I am still confused as to why it needs a "file name" when the database may be on another computer and I'm connecting to it via TCP/IP or something, but that I will figure out at another time.

Right now I'm trying to do something very simple.
I've created a database and defined where the file is located (C:\db\mydb.mdf) and now I want to create the datasource/dataset for that database.

But I keep getting the same errors. First i needed to the the sp_configure for the user thingy which I did and thought that was the end of it, but it wasn't.
Through the Management Studio Express I added some user accounts, set up the mixed authentication mode (to allow "users" to log in via SQL Server Authentication), making the user logins part of the "Server" and then went to the "user mappings" of the user accounts and added them as public to the database I want to access with those users. one of which (my own) i flagged as db_owner to hopefully grant that user "god" privileges over the database. So I figure I'm going to need to learn what all those db_* roles actually mean but again save that for later.

When I use windows Authentication in the Add DataSource Wizard I get this error:

"User does not have permission to perform this action"

If I set the User Instance advanced property to false the error complains that the db is in use by another process or is on a unc share drive.

When I try to use SQL Server Authentication with an account listed not only as public but as db_owner for that specific database i get this error:

Failedto generate a user instance of SQL Server. Only an integrated connection can generate a user instance. The connection will be closed.

When I turn off the User Instance I get this error:

CREATE DATABASE permission denied in database 'master',
an attempt to attach an auto-named database for file C:\db\mydb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Now the funny thing is that I've managed to get the Server Explorer to connect to the database with both Windows and SQL Server authentication but I can't seem to get the system to add it as a "data source".

In the end I believe my issue is that I don't know how to correctly administer the Sql Server Express database server with Multiple "user accounts" or using the windows log in so that I can access a server database (not one specifically local to the system which is running my application) and do some processing with that db.

Any help (or references to some straight forward articles on the subject) would be most appreciated.

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[JohnH]

why it needs a "file name"

The Express data providers only allow local file connections through the "Add new data source" dialog.

Here is an help article about Connecting to SQL Server Express User Instances (ADO.NET). This quote is relevant:

To connect to a user instance, Windows Authentication is required; SQL Server logins are not supported.

There's also this:

User instances are enabled by default when SQL Server Express is installed

Though I'm sure I was asked at some point during one of my installations of SqlX if I wanted to enable it or not. I'm also sure I have worked with VBX and SqlX with user instances both enabled/disabled right out of the box with no trouble and no special configurations done by me, but I also see you are exploring more options than I was.

 

[JaedenRuiner]

The Express data providers only allow local file connections through the "Add new data source" dialog.

Okay. Given that the Express Data Providers only allow local file connections to the DB, does that exlude the "Typed Dataset"?

Basically, i'm asking that right now I've set up the Sql Express on my system. I've created the database and everthing with it, and am working to write my code to handle the database management as well as the actual application i'm working with.

However, Upon deployment, this database will most likely be moved to a server on our company network, one which most likely I will have to install and set up. This server (using express) will only be for simple side project databases like this one, which is why it is not as necessary for me to know "everything" about database management, but for many reasons, I intend to finish the executable, and then it will be provided to those employees that need to use it. When they run the exe, all of them will be accessing the same database, so that database must 'network accessible' not a "local file". With that being said, I do not believe I can link to the file in a unc share path to the DBFilename, which means I would be accessing the database through the "initial Catalog" or some such property of the connection string.

My Current DataSource is ".\SQLEXPRESS" for the local system's database server name, but upon deployment I was planning to change it to "\\ServerName\SQLEXPRESS" (or something of that effect). Can't i then change the connection string for my Typed DataSet that was "generated" by the designer to instead of connecting to my "local file" to the network shared server and the database instance that exists there?

(I've already enabled the TCP/IP and named Piped communication access with my server, but perhaps i'll need to study more to allow network users to access the server, but I would think the principles are logical.)

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[JohnH]

Can't i then change the connection string

You can change the connection/string at runtime by accessing the TA.Connection property. I have never tried changing a "Express" connection in Designer pointing to a network service.