I am trying to set up a DB in SQL Server Express on a VPS Basic account. I am used to working with a shared SQL Server 2000 host and shared web host with a control panel that simply asks for a DB name, username, and password and then creates the DB for me. Then I can connect with a connection string like this: "Provider=SQLOLEDB; Data Source=sqlserveraddress.net,1433; Network Library=DBMSSOCN; Initial Catalog=databasename;User ID=username;Password= password;"

So, I have several questions:

1) How do I create a user/pwd for a SQL Server Express database? Is this a Windows user or is there some way to create a user/pwd in SQL Server Express?

2) What is the connection string for connecting to the SQL Server Express from a web app that is being served locally?

3) What is the connection string for connecting to the SQL Server Express from my desktop if I am using some SQL management software?

4) Is it true that SSE does not allow importing and exporting? Or is that just a limitation of Microsoft's management software?

I'll try to answer what I could..

1. To create a username password, on the left pane in Management Studio, scroll to the security folder and right click it and "Add User". In that dialog, you will see how to assign permissions per database.

2. We use only coldfusion, so I can only help you with that. If that is the case, you have to setup a DSN from the CF administrator.

3. I wouldn't reccomend enabling this unless you have your server secure. But if you really want to, see this support article

4. This is true in Management Studio EXPRESS. NOT in regular Management Studio. Management Studio Express is a stripped down version of the regular Management Studio. Regular management studio is only available via the SQL Server install CD

Thanks. Your advice on 1) worked fine, except now a whole can of worms has been opened up, what with the difference between schema and owners introduced in SQL Server 2005, and all the permissions stuff I had been oblivious to before now.

As for 2), I did a lot of googling and came up with over a dozen possibilities for connection strings. Here's what finally worked for me:

"Provider = SQLNCLI;Data Source = .\SQLEXPRESS;Initial Catalog = myDatabase;User ID = myUserID;Password = myPassword;"

or:

"Provider = SQLNCLI;Data Source = localhost\SQLEXPRESS;Initial Catalog = myDatabase;User ID = myUserID;Password = myPassword;"

None of other strings I found worked, but they included various parameters which I am curious about:

Integrated Security=True;
Integrated Security=SSPI;
Pooling=False;
User Instance=True;

For more info on connection strings see:
http://www.aspfaq.com/sql2005/show.asp?id=3
http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx
http://codebetter.com/blogs/jeffrey.palermo/archive/2005/11/04/134284.aspx

3) was cake, once I created a UID and PWD in 1) and did all this:
http://www.hostmysite.com/support/vps/windows/remotesqlexp/

The client software I'm using just required the UID, PWD, and server IP address. Yes, I am worried about the security of this kind of connection, which is one of the reasons I am moving from shared hosting to VPS or dedicated. I am thinking of using SSH to make a secure connection from the desktop to the VPS and then connect to SSE over the SSH, but I haven't figured this out yet (I don't know the first thing about SSH and I'm wondering if this is even going to be possible). The other alternative is that SSE supposedly can generate a self-signed SSL certificate. Then a secure connection can be made from the desktop provided the client software is SSL capable. Any advice on either of these methods would be very welcome.

4) I was able to import data to SSE using SQL client software, so that is not a problem. (Various web pages, including Microsoft's own comparison table for SQL Server versions, indicated that this was not possible.)

Yeah ss it true that SSE does not allow importing and exporting? Or is that just a limitation of Microsoft's management software??? i really don't know...




_________________
Inventory Control