Feedback on a non-domain machine

[Eric Lachance]

I've already contacted support via email, but just in case someone here has a little insight into this, maybe it will be faster.

I've finally installed Feedback on a live server, which is publicly accessible. However, after installing it and starting it for the first time, I realized once again that the window that asks for the database and/or IIS login is really f*cking confusing and not at all user friendly. The situation is that my network admin installed this machine but did not join it to the domain. However, the SQL server I'm connecting to (remotely, not on the same server) IS on a domain.

If I read the documentation for Feedback (which lacks a lot of details regarding this window in particular), and the window itself, it seems to me that both the web server and the SQL server have to be on the same domain (so my network admin would have to join the machine) and also need to share a username... But it's pretty unclear to me what this username shares, exactly. The window says, and I quote: "When the SQL Server is located on a separate computer from the one running IIS, you must specify a domain user that is common to both computers. Otherwise, this is optional. If you are unsure, it is recommended to leave this option disabled".

However, this is an SQL SETUP window and the paragraph mentions nothing about the actual SQL server authentication. So, I have no idea if the username, while shared with both machines on the same domain, must also be present in the SQL server. And if it does, does it really need to be sysadmin (something my SQL administrator frown upon hearing and went "wtf is this software security crap!!"/$%!"/%$")?

Can anyone shed a little light on this mess?

[RamonS]

There are two ways to authenticate against an SQL server. One is via Windows authentication (which requires a domain or a local user account) and one is with SQL Server authentication, which requires user accounts to be set up within SQL Server. On top of that, if you want to connect remotely via TCP/IP the protocol needs to be enabled for remote connections, which also requires proper configuration of the firewall(s) if there are any (and in your case they should be in place). Which authentication is allowed can be set on the SQL Server. It can be either one or both.

Here is what I'd do. First of all make sure that firewalls are set to allow for TCP traffic to go through at port 1433, which is the default SQL Server port (if there is a need to change the port, I can get you information for that). Then make sure that you know which way to authenticate (ask the network/IT admin or DBA) and get the applicable user name and password. Since your web server is not on the domain my assumption is that SQL Server authentication is to go, but I am not that much of an MSSQL guru.
Next up download MSSQL Management Studio Express and install it on the web server. With that to connect to the SQL Server using the user name and password provided and make sure you have sufficient rights (if Feedback Server insists on creating its own database you need more rights, if you can specify a database you need only read/write rights to that database).
Only after you managed to connect to the SQL Server this way I'd start bothering with Feedback Server, because by then all the nasty obstacles that come with MSSQL are out of the way. You can uninstall Management Studio once everything is set. Any other issues that may come up are probably IIS related.

Good luck! You'll need it!

[Eric Lachance]

Thanks Ramon,

I've sent your reply to my IT staff which, thankfully, is very competent. I think one issue is that the SQL server doesn't show in the list of available ones. It's not the webserver that blocks anything though - I can see other servers, even one that's on a VM installed by myself! So the server may have named pipes or TCP disabled, IT will look into this.

I can't logon to that VM server whether I add a domain user or a sql user though, I always get "Server is not operational" whether I put the webserver or SQL server's name in the "Domain" box, and whether I put a valid or invalid user/pass combination.

What confuses me greatly is that the checkmark on top of this authentication box is called "Manually set IIS authentication settings" which, to me, means I'm giving IIS credentials, not SQL ones. Between that interpretation and the one where I'm giving IIS credentials to connect to an SQL server, or the one where IIS has nothing to do with it, is a very confusing line.

[RamonS]

Maybe it means giving IIS credentials to authenticate against MSSQL??
I know it isn't helpful, but the XAMPP stack is way easier to deal with than this IIS/MSSQL garbage. A company even pays me to endure this constant abuse.

[Eric Lachance]

I'm with you on that one. Our PrintShop Mail Web software uses Apache + MySQL + PHP to work, it installs everything on its own. I'm building a karaoke software with one of my friends which will use SQLite, even better.

In regards to my issue, installing SQL Management Studio Express and then connecting to the same DB I'm trying to from Feedback works fine - which means it seems IIS is the culprit.

What really peeves my IT admin is that it seems that SQL authentication doesn't work from Feedback and it seems MadCap really wants us to use Windows Authentication with a domain username shared between the machines. This really makes him wonder who's the bozo that thought having a domain-connected machine as a web server accessible by the public was a good idea. He does NOT want this to be the case.

You seem to mention in your first reply that SQL Server authentication is possible, but have you ever tried it? It doesn't seem like it's possible from where I'm standing.

[RamonS]

SQL Server Authentication needs to be turned on. I just checked and it seems as if this can only be done during installation. When SQL Server Authentication is enabled you MUST specify an sa password. I don't install production servers, but I always pick mixed mode so that both work. No clue if that is considered insecure or less than ideal for production servers. As for the publicly facing web server on the domain...yea, that won't happen.

You could fire up a VM with a mixed mode SQL Server for testing purposes, but in the end it won't solve the original issue.

[RamonS]

I just checked and it seems as if this can only be done during installation.

I take that back, see here: http://www.hosting.com/support/vps/windows/mixedmode

[Eric Lachance]

Ramon,

It's already a mixed authentication server, so that's not an issue... In terms of login rights, we tried both the one specifically meant for the feedback server (which I believe does have rights to create a database) as well as the Administrator account which is both the SA for the SQL server and an admin on the machine. Still get "Server is not operational", which doesn't seems like an authentication issue. This is on 2 different servers, both mine (not on a domain) and the production sql server (which is on the domain)

The only time I can get anything else than "server is not operational" to appear is when I put our actual domain in the box, then I get a wrong user/pass (which is normal since the username doesn't exist on the domain and we don't want it to be there).

I've email the rep who sold us the whole shebang to start with (we have EVERYTHING madcap sells except Blaze and Feedback service) to ask they forego charging $100 for this call... let's wait and see I guess.

[Eric Lachance]

So the official answer to this was:

Either
You install SQL on the same machine as Feedback
or
You join your IIS/Feedback server to a domain, create a user shared with both machines and give this user access to the database.

The first option is what we chose for the time being but it adds maintenance overhead and slows down the server for no reason.
The second option, according to my IT staff, is an unacceptable security risk.

Not satisfied with the answer, but I guess we'll just have to deal with SQL Server Express on the Feedback machine for now.

Thanks for your help, RamonS!

[RamonS]

Make sure you file a bug report. This design is quite flawed.

[Eric Lachance]

I've already made my concerns known to the support technician and our sales rep, I'm confident they will evaluate this for a future version.

[RamonS]

I came across a similar case today with an entirely different app. I could solve the problem by turning on the SQL Server Agent. I don't know if it will help in your case, but it might be worthwhile to check if it is running. Without the agent clients can have a tough time finding and connecting to the SQL server.

[Eric Lachance]

Ramon,

Thanks for the tip, but finding the server wasn't the issue. One server I could see, the other I couldn't, and I couldn't connect to either. In any case, support confirmed that it was required to have both on the same domain, so we're not looking any further.