Connecting to VCNSQL
-
I've previously just worked with local databases so I'm a newb at connecting to remote servers.
My MS SQL database is hosted on a shared hosting platform at Verio which was recently bought by Endurance International Group Hldgs Inc. Prior to Verio's migration to Endurance's servers, the connection string I used for the 'server name' section of the 'connect to server' dialogue box in SSMS was 198.170.241.132,1433\SQLExpress1. That box is going offline soon and my site has been migrated so I need to connect to a database on the new servers.
I've been able to create a MS SQL database on Endurance's servers through their web portal. However Endurance support tell me they can't give out an IP address for the server the database is hosted on. I'm trying to figure out how I would connect to the server without an IP address (their support hasn't been much help here and the knowledgebase article on it at the Verio website here gives a 500 error http://www.verio.com/knowledgebase/beta/article.bml?ArticleID=1829).
Here's all the information Endurance have given me regarding the new database I just created on the shared hosting platform:
MS SQL Host:VCNSQL102
MS SQL Database:mydbname
MS SQL Login:mydblogin
Password:xxxxxxx
There is a web portal which I can login through where I can see the database and the local string on the server it is hosted at seems to be VCNSQL102\I102 \ User Databases \ mydbname
My question is - how do I connect to that database remotely with SSMS if Endurance won't give me the IP? Is it possible with the information they've given me? If so what exactly should I enter in the 'connect to server' dialogue box?
Thanks in advance for any help! Since I'm a newb at connecting to remote servers the more explicit the instructions the better!
-
You probably can't. You can connect to an instance by using it the server and instance names, however, at the end of the day all your computer and/or domain controller does is resolves that into an IP and port number.
For example, you might have a SQL Server might be called "SQLSERVER02", which has an instance on it called "MSSQL16Dev". You could open SSMS and type "SQLSERVER02\MSSQL16Dev" into it and then connect. Your PC would get the IP address for "SQLSERVER02", let's say it's 192.168.1.12, and then contacts that server. It then negotiates with the Server and lets it know that the instance name it wants to connect to, "MSSQL16Dev", which it is informed is running on port 1483. it then starts it's communicating with the Server address at 192.168.1.12, over port 1483.
Supplying the IP address and port number (you're supplying the instance name as well, but that's not a problem), effectively misses out the resolution of those names at the start of the negotiation, but that's it.
I can'#t think, off hand, of any other way you could connect. Of course, your host may be able to offer support on it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thanks for the reply Thom. How do you think people generally work with these servers then? If they can't connect through SSMS due to not having the IP address, what would they generally do? Just execute scripts through a web console of the shared hosting provider?
-
I don't have an answer for that I'm afraid, I've not used external hosting.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
caspersql (11/18/2016)
Thanks for the reply Thom. How do you think people generally work with these servers then? If they can't connect through SSMS due to not having the IP address, what would they generally do? Just execute scripts through a web console of the shared hosting provider?I suggest that you ask the hosting provider how their other SQL Server customers connect to their databases.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks Phil. I had submitted a support ticket a few days ago to Verio's new support team requesting that info but received nothing in response. Can't reach them on the phone either despite waits of 40 minutes plus. I think their ticketing system has been overcome by customers needing help migrating from the old servers.
They did however fix the knowledgebase article I mentioned above that covers connecting to MS SQL databases hosted with Verio so that it is visible now here http://www.verio.com/knowledgebase/beta/article.bml?ArticleID=1829. It says remote connections are not possible and that databases must be managed and scripts executed through Verio's web portal tool called Littleadmin.
Which is fine - I've been able to log in to the web portal, create a database, execute scripts to create tables etc. Now I'm trying to figure out how to get the PHP code that runs my website to connect to it. The code previously used an IP to do that, but that can't be done because Verio now won't give out the IP for the server the MS SQL database is hosted on.
The knowledgebase article that has been restored says to modify web.config and place a connection string in it as follows:
for the example login info which is
There is a settings.php file on my server with connection info which I tried to update to the following to see if it would work with my db settings using the format for the new Verio servers shown above but to no avail (previously the 'DB_SERVER' variable here included the IP address of the old server):
$GLOBALS['DB_DATABASE']='mydbnamegoeshere';
$GLOBALS['DB_SERVER']='VCNSQL102';
$GLOBALS['DB_USERNAME']='myusernamegoeshere';
$GLOBALS['DB_PASSWORD']='mypasswordgoeshere';
$GLOBALS['DB_VENDOR']='MSSQL';
The above is nothing like the connection string in the Verio knowledgebase article. I think the web.config mentioned in the knowledgebase article is a file for a website run by ASP.net so I guess the connection string in the article is tailored for ASP.NET not PHP. I was wondering if there is anyone here who has experience with both PHP and MS SQL and could tell me how to "translate" the connection string into PHP so that I can add the relevant text to the PHP code on my server to get it working?
-
A freelancer helped me to get this working. While I don't profess to know for sure that he didn't do more than what I'm about to try (inadequately) to explain, I don't want to be a DenverCoder9 so I'll share what little I do know. Maybe it'll at least point the next guy who has similar issues towards the problem.
1.
.htaccess in the root folder needed to be rewritten with pointers to the subfolder that is root for the site in question. The relevant code for the site that uses the MS SQL db (the PHP code for which is in a subfolder of the root directory of my site because it runs as a subdomain) is:
RewriteEngine on### subdomain redirect v3 ###
RewriteCond %{HTTP_HOST} ^(www.)?subdomainurl.com$
RewriteCond %{REQUEST_URI} !^/subdomainfoldername/
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ /subdomainfoldername/$1
RewriteCond %{HTTP_HOST} ^(www.)?subdomainurl.com$
RewriteRule ^(/)?$ subdomainfoldername/$1 [L]
2. The PHP file that contained connection settings was modified to add
define ('DF', '\subdomainfolder'); // here the subdomain folder is defined for appending to $_SERVER['DOCUMENT_ROOT'] for path construction to the folder, where the original site is
$GLOBALS['APPLICATION_DIR']=$_SERVER['DOCUMENT_ROOT'].DF;
$GLOBALS['APPLICATION_CLASSES_DIR']=$GLOBALS['APPLICATION_DIR'].'\classes\';
$GLOBALS['APPLICATION_FORMS_DIR']=$GLOBALS['APPLICATION_DIR'].'\forms\';
$GLOBALS['APPLICATION_SKINS_DIR']=$GLOBALS['APPLICATION_DIR'].'\skins\';
$GLOBALS['APPLICATION_USER_FILES_DIR']=$GLOBALS['APPLICATION_DIR'].'\userFiles\';
$GLOBALS['APPLICATION_HELP_FILES_DIR']=$GLOBALS['APPLICATION_DIR'].'\helpFiles\';
3. The following was added for connecting to the database (new sessions directory settings). Sorta amazes me that this works given that the MS SQL DB is on a different server to the one that this code runs from. There's probably more to it that I don't know about - this isn't my area.
session_save_path($GLOBALS['APPLICATION_DIR'].'\session\');$GLOBALS['DB_DATABASE']='mydbnamegoeshere';
$GLOBALS['DB_SERVER']='VCNSQL102,1433\I102'; //Server, port # and instance for DB. Note there's no IP needed which is what I was confused about in my original question
$GLOBALS['DB_USERNAME']='mydbusernamegoeshere';
$GLOBALS['DB_PASSWORD']='mydbpasswordgoeshere!';
$GLOBALS['DB_VENDOR']='MSSQL';
4. This line was commented out from the index.php file that users land on when they visit the site (because save path is set in the new code added in #3 above):
session_save_path("/home/users/web/b1234/apo.username/cgi-bin/tmp"); -
Hello caspersql,
I am in the same situation you were with verio, except I have not used a local connection set up and I wanted to ask how you to make that happen.
And I have spent 90 minutes on hold with them multiple occasions, so I feel that pain.
I have just installed mysql on my local system.
Any shortcuts or instructions you can supply for a complete newbie on how to connect to the VCNSQL102 verio database from a local computer would be very appreciated.
Thank you.
- Tatay_2016 - Tuesday, January 17, 2017 8:36 PM
Hi Tatay,
Yes their customer support is the worst I've ever experienced. As soon as I find time to move I'll be gone to another provider.
With regard to connection to the database, unfortunately the final answer I got was that a connection from a program running on your local system (e.g. mysql) is not possible. That was for a MS SQL database not a mysql database so it could be different for mysql, worth checking.
The way they require users to work with MS SQL databases is: log into the verio control panel at https://www.verio.com/controlpanel/beta/ you can see there are two different options for MySQL and MS SQL. I'm guessing if you're using mysql you'll want the former but I was working with the latter.
- Login to https://www.verio.com/controlpanel/beta/
- Click "MS SQL Database" under "Website"
- If you've created your database you should see it here (this is where I see the server name VCNSQL102).
- Click the login name for your database which is a hyperlink
- That brings you to a site where you can manage your MS SQL database. You can run scripts on it in the browser using the tools there.
However when I do the above right now it says 'service unavailable' - Verio just sucks. I'll submit a ticket about this now, bet there'll be no reply in 3 months time like with my other tickets.I did come across a few mysql articles on the Verio help site before which might be of use to you by searching for sql here - http://www.verio.com/knowledgebase/beta/
- caspersql - Wednesday, January 18, 2017 2:07 AM
Hello caspersql,
Thank you for the reply.
Yes we were running mssql at Verio (still are but we are moving out and over to mysql as we speak), however we still need to access that database in the short term until we get all set up elsewhere. That's part of my project.
Did the script you posted above here work for you at Verio? (when the link was working, that is) I was not aware that was the only way to access the database, so thanks for that info that helps.
Also, We are moving to 1and1 hosting. They were rated among the top three of 2016 for hosting on several review sites, they have good pricing, equal or better services, AND they answer their phones. 🙂
PS the first week verio moved us over to the new platform, I put in two service tickets dated a week apart. In between my service ticket numbers were 8,200 other service ticket numbers. We are not alone.
- Tatay_2016 - Wednesday, January 18, 2017 9:47 AM
Just to be sure we're on the same page, that script wasn't for connecting from my local system to Verio, it was code that runs remotely on the website hosted by Verio and accesses the ms sql database which is held on a different server run by Verio. Yes I got it to work with the help of a freelancer who is a PHP web developer (I don't know PHP - just T-SQL). I can PM you his contact details if that helps.
Also not sure if this helps but during the migration I noticed that the login to the MS sql server worked past the November deadline when they said they were going to switch access to it off. Not sure if it's still up or not but if you need to work on it without providing access to the outside world, that would be one option (you can just use the old connection details you used to use to access it).
- caspersql - Friday, November 18, 2016 8:17 AM
An alternative would be via a VPN tunnel directly to the server but the hoster would be responsible for providing the Information for that.
I assume this is an SQL Server in a Windows VM hosted by the Provider or iis it in Azure?
- caspersql - Wednesday, January 18, 2017 10:38 AM
Hi,
Yes, I realize that it was code intended to be run on the web site using my little admin.
Always good to have a resource for a reliable php programmer so PM me his info, thanks.
Regarding the migration and login. We were migrated on a Tuesday in November with no warning, and all our emails stopped working at that time. After repointing the DNS our emails were back up, but 10-15 different functions failed. Over time they did fix some of those things but several still don't work, and we've determined its a server side issue based on the error codes we were getting. However like you we cannot get any assistance from them. They simply marked tickets as "resolved", even if they weren't. We gave up trying.
Once we were migrated we could not go back and access any of the old connections. I did try that.
So I'm just waiting to move. It's not easy. Moving is hard enough but moving from MS to MY sql is another challenge but its the right one.
Thanks for the reply.
- Tatay_2016 - Thursday, January 19, 2017 12:14 PM
Yes same happened to us. They had sent an email saying that it would happen at end November so I planned to migrate in mid-Nov, then bam on that Tuesday in early November they attempted an auto-migrate with no notice, in the process of which they broke our acess to our sites and email and to top it off they had a hardware meltdown that left them unable to restore access even on the legacy servers for a full week. After the migration things are not much better - everything is buggy (even their catch-all email accounts didn't work the way they were initially configured). EIGI/Verio are such a shower of incompetents I can't believe they are a public company.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply