SQL Express - unable to connect remotely

[supermop2000]

I've run into some major headaches trying to setup my ASG RD environment to use SQL Express remotely. I've been using a local SQL Express environment for some time on one of my terminal servers at work. However, I am now needing to push this out to some more of my team, so I figured I'd just get everyone on the same master database that I've been using on the SQL Express instance.

I'm not exactly new to MSSQL - been a sysadmin for over 12 years looking after SQL among many other, and I've even ran an ASG RD database before, except this was on full blown SQL at my previous employer.

I have enabled SQL Express to be remotely accessed via SQL config manager (enabling the named pipes and TCP/IP services, and restarted), and setup a security group on our domain to have full SA rights on the SQL Express instance, as well as explicit full permissions to the ASG database. I run into an issue when trying to set up ASG remotely - I enter the server name, database name, and use integrated authentication (the user is in the group configured earlier), and the test connection is successful. As soon as I hit ok to confirm it all, I get an error message every single time saying "Object reference not set to an instance of an object". I'm no SQL error expert, but that make no sense at all.

I never had this issue using full blown SQL - is there anything I'm doing wrong here??

EDIT: I am trying to paste screenshots, but this message board is very broken - pasted images just convert to weird encrypted ASCII strings.....

[DevOma]

Hi,

SQLExpress should work also for remote access - we will find a solution :-) The error message seems to be a ASGRD error message - did you run ASGRD as Administrator or just start the ASGRDElevated.exe to ensure that you can save data to "Program Data" instead of your User Profile directory?!? It should be reported by an error message but this is the first we can check - error on accessing your local Program Data folder.

To paste images you should use "New Reply" intead of "Quick Reply" - there you can add images or other attachments!

[supermop2000]

Thanks. I still get the same error message running elevated.

When I run as Administrator, I have to use my domain admin credentials (due to UAC policy) and this then loads the wrong user profile! Is there any other way to do this other than granting everyone admin access to their machines?

Cheers

[supermop2000]

Ok, so it looks like you NEED admin rights to be able to basically use this application, which is a bit annoying. Why did the devs choose to use the %PROGDATA% folder instead of user folders for this?

I had to temporarily add my non Superuser account to my laptop as a local admin (which I had to do remotely through AD on another server) and that allowed me to connect.

However, the issue I have now is that I am connected to the database apparently, but the connections tree is completely empty. Connected to the exact same DB on my other computer and I see all my connections.

I have granted user rights on the SQL database - basically given SA access to the AD group I'm using to control this but apparently I cant see it as other users? Any ideas?

[supermop2000]

Upon further investigation, it looks like I am connecting to the same database, but this new user cannot see anything that already exists.

I've granted DBO on the whole database, but all I can see is objects that this user creates.

The whole thing was originally setup with my SU account, and I've setup a group to grant permissions on the SQL server. This group contains my SU account and non-SU account. My non SU account cannot see anything my SU account created, but it can create new objects (e.g. folders/connections) - these new objects can be seen by all users, but the hundreds of existing objects can only be seen by my SU account, which was the original creator of the DB.

I'm a bit stumped here!

[supermop2000]

Another update:

I took a backup of all connections/creds etc and then deleted the originals from the database - so had a completely empty DB.

I then tried importing them as the new user, which worked fine. The new user could see all the objects. HOWEVER, my SU account - the original DB creator - is now unable to see anything!!

What the hell is going on here?

[supermop2000]

Now I have attempted completely deleting the entire DB, and then re-creating it as the new user (using the groups we set up earlier).

Getting the same weird behaviour - whichever user imports the connections is the ONLY user that can see them. No other users are able to see them.

The only one object that all users can see was the test object that I created earlier.

This seems to be some weird embedded permissions on the actual objects, and NOT SQL related by the looks of it.

Any ideas?

[DevOma]

Ok - that has nothing to do with the SQL - I guess the main folders are private? So only the creator/owner can see - check it by opening a folder (1st level under Connections) - try to use "Convert to public" - there should be a question to make all sub objects also public that you have to answer with Yes!

[supermop2000]

Ok - that has nothing to do with the SQL - I guess the main folders are private? So only the creator/owner can see - check it by opening a folder (1st level under Connections) - try to use "Convert to public" - there should be a question to make all sub objects also public that you have to answer with Yes!

Aah thank you! I had no idea this was even an option - not sure why it was set to private by default, but that was the issue!

Looks like I'm all working now. Thanks.