SQL Express 2008 issues

[NLC]

Does anyone have any tips/working solutions for installing on Able on SQL Express 2008? Do I need to treat it like a true SQL system and create the database first? Does the SQL Browser need to be running? Attempting the install with the SQL Express 2005 selection won't work:

The specified database connection could not be opened.
Connection String: Data Source=.\SQLExpress;Integrated Security=True;AttachDbFileName=|DataDirectory|\AbleCommerce.mdf;User Instance=true;

[afm]

The connection string you quoted is the format for connecting a "user instance" database. Two common things can go wrong with this:

1. The SQL Server instance is not called SQLExpress. I'm pretty sure that is the default, but you could easily have changed it during the install. I usually change mine to SQLExpress2008 so I know what version it is. Be sure to use the right name in the connection string.

2. The SQL Server instance does not have the "user instance" database feature enabled. Again, I'm pretty sure the default is to enable "user instance" databases, but you could have turned this off during install. I don't know how to check for this without re-running the install.

SQL Browser does not need to be running.

You can use SQL Server Express as a server and create a database for AbleCommerce instead of using the "user instance" database. In that case, create the database and then tell the AbleCommerce installer that you are using SQL Server.

[NLC]

From what I can tell, which may not be accurate, the SQL server instance is call SQLEXPRESS. I found the reference in my .NET Connection String Manager within Plesk (Windows Server 2003 VPS). I also found in this connection string that User Instance was set to true, so I would think it is safe to assume that it is enabled:

Connection Name: LocalSqlServer
Connection Param: data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true

What does Able Commerce use for a user connection? Will I need to create a user for the db with specific permission (like datareader, datawriter) or does it use a built-in account?

[AbleMods]

Your SQL connection string is set to integrated security. Doesn't it need to be set to SQL credentials with the correct SQL authentication?

An easy way to test building your connection string is to create an ODBC SQL connection. Then you can at least get more immediate feedback as to whether the credentials and settings are correct.

[afm]

What does Able Commerce use for a user connection? Will I need to create a user for the db with specific permission (like datareader, datawriter) or does it use a built-in account?

The connection string says to use Integrated Security, so AbleCommerce is connecting to SQL Express using its account (probably NETWORK SERVICE). So is Plesk. That should be fine for user instance databases.

I wonder if SQL Express is unable to open the database file in read/write mode. Try comparing the AbleCommerce App_Data directory permissions to the Plesk App_Data directory permissions.

[NLC]

Well, after a few nights of work, we're getting closer. To eliminate a variable, I had 2008 pulled and replaced with SQLExpress 2005, although we're running into the same issue. It looks like the ASPNET user that Plesk created (not the default NETWORK SERVICE user) does not have rights to the local database, even when we explicitly state so. Thankfully, my host has been patience with this install and has been helping quite a bit. They were able to get the app talking with the database, however now my application pool keeps crashing.

If anyone has any ideas, feel free to post them. Otherwise I'll keep posting progress. When this is all said and done, I hope to have a documented procedure on what NOT to do when installing AC with Plesk on a Win VPS.

[AbleMods]

Maybe I'm missing the boat here, but it sounds like the whole issue involves the effort to use integrated security. Why not just switch it to SQL credentials and guarantee you control the authentication?

[NLC]

Maybe I'm missing the boat here, but it sounds like the whole issue involves the effort to use integrated security. Why not just switch it to SQL credentials and guarantee you control the authentication?

Good point, and that is what was done in the end. Once we switched to SQL credentials, it installed without a hitch (well, maybe one hitch).

[AbleMods]

Excellent. One or two hitches is ok. Can't-open-the-darn-store-homepage is not an ok hitch

[Brewhaus]

I am having the same issue, but with SQL Server 2008 Standard, and we are running in mixed mode and using SQL authentication:

The specified database connection could not be opened.

Connection String: Server=.;Database=HotSauceDepot;Uid=HotSauce;Pwd=(removed for security);

Any thoughts on this? I am becoming increasingly frustrated as this is the last step before finally being able to migrate to a much needed upgraded server.

[AbleMods]

Usually there's an EXXXX error number associated with the error.

If you have access to the web server console, you could create an ODBC connection using the same connection info. Then you can test the connection in the ODBC Administrator and possibly get a more descriptive error message.

Without knowing more about the hosting environment, it's difficult to be of much help. There could be a firewall issue, either in the network or Windows Firewall blocking the traffic. Or a SQL permissions issue preventing the SQL user from accessing the db.

Do you have direct access to your servers or is this a shared hosting environment?

[Brewhaus]

First I tried disabling the firewall entirely, followed by trying to run the installation directly on the server. Both gave the same result. My feeling is that this is most likely a SQL permission issue, but I cannot for the life of me figure it out. Sorry, but I don't know how I would go about setting up an ODBC connection on the server to (hopefully) pull an error code, but am all ears.

The new server specs are:
Windows Server 2008 R2 (ie. IIS 7.5)
SQL 2008 Standard
AC is running on .NET4.0

Rick

[AbleMods]

Ok and all of that is on the same single server?

Try loading SQL studio management and see if you can log into it with your database credentials.

[Brewhaus]

I can log into SQL Management Studio using those credentials, BUT I cannot access the DB. If I log into SQL Management Studio with Windows Authentication I can access the DB. The user is listed under Security --> Users, but I assume it somehow is not getting given the permission that it needs (or am I way off base?)

[AbleMods]

You're close.

Log into Management Studio using your windows authentication.

Expand Security
Expand Users
RIght-click your database user and choose properties.
Choose Server Roles

What server roles are checked for your database user account?

[Brewhaus]

Hmmm... maybe they changed things, as I do not have Server --> Security --> Users. I assume that is the same as my --> Security --> Logins. If so, then the only Server Role selected is Public. Unchecked options are bulkadmin, dbcreator, diskadmin, processadmin, securityadmin, serveradmin, setupadmin, sysadmin.

If I go to the DB (HotSauceDepot) --> Users, the user HotSauce has db_accessadmin, db_datareader, db_datawriter, db_ddladmin, db_securityadmin selected.

[AbleMods]

Something isn't right with your SQL setup or your management studio.

Both my dev SQL 2008 express and my web hosting business servers have the the server->security->logins option.

Log in as your sa account and see if its there.

[Brewhaus]

I do have Server --> Security --> Logins. You had specified Server --> Security --> Users, and I wanted to note that mine was showing slightly differently. I figured that it was the same, but wanted to note the minor difference just in case. I have attached a screenshot to make sure that we are talking about the same thing.

Any other thoughts?

[AbleMods]

Set all the server roles for the db user in the server, security, logins area. Then log out and back in as your db user, see if you can browse to your database now.

Permissions at the db level don't come into play until the SQL server engine permissions are processed. So if the server login doesn't have rights, the db permissions won't matter.

[Brewhaus]

That did it! And I was able to connect and complete the installation. Now comes the question- which roles should actually be set? Obviously one of the new roles needed to be added- should I remove one at a time until I cannot connect again? I am just wondering about the security side of things.

[AbleMods]

Glad to hear you got it working.

The security of your installation is beyond the scope of the help I can provide here. I would recommend getting a qualified SQL administrator to review your installation. Proper server security is not something you want to learn the hard way.