Steps for connecting to MSSQL 2005 or 2008 server through SQL Server Management Studio:
The MSSQL user(Secure Enterprise Manager) is not enabled for your domain by default. You will have to submit a separate ticket for it to the support team, so that the feature can be enabled from our end. Make sure that your plan supports this feature. Contact support if you are unsure about this feature for your domain. We allow only Secure connections to our MSSQL server. All data and logins are encrypted.
The MSSQL databases for the following servers are hosted on db1.3essentials.com.
web16, web18, web20, web22, web24
The MSSQL databases for the following servers are hosted on db2.3essentials.com.
web26
Connecting through Secure Enterprise Manager is a two step process.
I. Establish a secure connection.
II. Connect to the SQL server.
I. There are two ways by which you secure your connection. (Use Only one)
A. Using Tunnelier SSH Client for Windows:
- Send a request to our support team to enable SSH/database access for your account
- Visit http://www.bitvise.com/download-area to download the Tunnelier client, make sure to read their licensing agreement to determine if you need to purchase the client or if you can use their free version.Install it.
- Create a Tunnelier profile for the SSH tunnel.
- Open the Bitvise Tunnelier. This application will display a tabbed windows.
- The first tab is the "Login" tab. Fill in the following fields:
- Host: Type in the name of the server where your web site is hosted; i.e., web14.3essentials.com, web16.3essentials.com, etc... if you're not sure where you are hosted, please submit a support request asking for this information.
- Port: By default this value is set to 22. It should remain.
- SSPI/Kerberos 5: Check this box
- Initial Method: The default value is "none". Change this to "password"
- Username: Your FTP UserID
- Password: The password to your FTP UserID
- Select the "Options" tab. Here you will want to uncheck the boxes "Open Terminal" and "Open SFTP"
- Select the "C2S Forwading" tab. Under this tab, select Add and fill in the following fields:
- Status: By default this will be defined as enabled. This should remain.
- Listen Interface: By default this will be defined as 127.0.0.1. This should remain.
- List. Port: Use 1434 unless you are running a SQL server on this port, in which case use an alternate port such as 1435
- Destination Host: Type the name of the server where your database is hosted. For example: db2.3essentials.com
- Dest. Port: Use 1433 which is the standard port for MSSQL.
- Comment: Fill in whatever you wish, but we usually recommend you type the server or a description, such as db1 or db2 or MSSQL
- Save the profile and select the Login button. You can also select the Login tab. The Login tab will display a running log of what Tunnelier is doing.
- Host Verification Window may popup. Choose Accept and Save
- A User Authentication banner may popup. Close the banner; If this banner appears, you MUST close it for the tunnel connection to be made.
- A message saying ' Initializing client-side client-2-server forwarding on 127.0.0.1:1434 succeeded.' verifies the successful connection.Minimize the tunnelier.
- Note: If you are unable to create a profile for Tunnelier, open a support ticket and we will provide one to you.
B. Using Plink:
- Send a request to our support team to enable SSH/database access for your account
- Download PLINK.EXE from the downloads section of this article or you can obtain it from the PuTTY Download Page
-
- There is no install for Plink. Simply put it wherever you wish. If you put it into your windows\system32 directory, you will then be able to access it from anywhere on your system without having to specify the path. That completes the initial setup. Next, launch an SSH tunnel from your system to ours
- Open a command prompt; Select Start > Run > type cmd
- If your site is hosted on web14, your MSSQL database would be on db1. So you would execute the following command:
plink.exe -ssh -pw password -N -L 127.0.0.1:1434:db1.3essentials.com:1433 username@web14.3essentials.com
In the above command:
a. Replace FTPUSER with your ftp userid
b. Replace FTPPASSWORD with your ftp password
c. Make sure to type this whole command on one line. It can wrap, just don't hit Enter until the entire command has been typed.
d. The -l is a lower case "L"
- You should receive a successful connection message. Leave the command window open. Plink must be running and connected while you are using the connection.
C. Using SSH from a Linux box:
The command below dhows the syntax to use to SSH to our systems.
ssh -l FTPUSER -L 1434:dbX.3essentials.com:1433 webYY.3essentials.com -N
Where X is the database server and YY is the web server. The example below uses web14 which means the database is db1.
ssh -l FTPUSER -L 1434:db1.3essentials.com:1433 web14.3essentials.com -N
This will prompt for a password. Use your FTP userid password to login.
II. Connecting to the database through SQL Server Management Studio:
SQL Server Management Studio can be downloaded from http://www.microsoft.com/express/Downloads/
IIa. Option 1 for configuring MSSQL Managment Studio - Specify Localhost and Port
- When using MSSQL Management Studio to connect, the simplest method is to specify the localhost and the port.
- In the Server name field enter 127.0.0.1,1434
NOTE: This is not a tpy0. There is a comma there. This is a convention used by MSSQL. Also note that if you connect to different servers, those servers would have different C2S Forwarding configurations in Tunnelier, each one on a different forwarding port. Simply specify that port here.
- Authentication type is SQL Server Authentication
- In the Login field type the database username that you created in the PLESK Control Panel for your database
- In the Password field type the password you created in the PLESK Control Panel for your database username
- You will be able to see your database and can add/edit the database.
IIb. Option 2 for configuring MSSQL Management Studio - Use an Alias
If you are connecting to different database servers, you may wish to setup aliases. Though you could use the above method and simply specify different ports, some may find an alias to be easier to remember. The steps below describe how to do this.
- Use the following values when configuring the MSSQL frontend (Enterprise Manager):
webY.3essentials.com is the web server where your domain is physically hosted. For example, web14.3essentials.com
dbX.3essentials.com is the MSSQL database server that hosts the database you created in the Plesk Control Panel. For example, db1.3essentials.com
- Add a Server Alias using the SQL Server Configuration Manager.
- Server Alias is a friendly name, and really can be anything you like.
- Select Start > Programs > Microsoft SQL Server 2005 or 2008 > Configuration Tools > SQL Server Configuration Manager
- With the SQL Server Configuration Manager open, Expand (click the + sign) SQL Native Client Configuration, right-click the 'Aliases' menu and choose 'New Alias'
- Alias Name: dbX.3essentials.com
Where is X is the database server on which your database is hosted. For example, db1.3essentials.com
- Port No: 1434 (assuming you are using 1434 for this and not 1435 or another)
- Protocol: TCP/IP
- Server : 127.0.0.1
- Configure SQL Server Management Studio Express
- In the Server name field enter the Alias. For example, db1.3essentials.com
- Authentication type is SQL Server Authentication
- In the Login field type the database username that you created in the PLESK Control Panel for your database
- In the Password field type the password you created in the PLESK Control Panel for your database username
- You will be able to see your database and can add/edit the database.
- Please contact support if you need further assistance.
KNOWN PROBLEMS
and other things we've found that can bite you with this particular configuration
- If you cannot connect, modify your ALIAS entry to the following:
- Alias Name: local
- Port No: 1434 (assuming you are using 1434 for this and not 1435 or another)
- Protocol: TCP/IP
- Sserver: 127.0.0.1
The use of "local" as the alias name is a quirk in SQL Server Management Studio.
- GOTCHA on SQL Server Configuration Manager 64-bit
- On SQL Server Configuration Manager 64-bit version, there are TWO options for the SQL Native Client Configuration:
SQL Native Client Configuration(32-bit)
SQL Native Client Configuration
The second is presumably 64-bit... you need to configure the 32bit version, as SQL 2005 Server Management Studio is 32-bit.
- The tunnel can be created on one line on linux/unix systems with the following ssh command:
ssh FTP_user@webYY.3essentials.com -L 1434:dbX.3essentials.com:1433 -N
This will prompt for a password. Supply the FTP password to login.
For example, if you wish to connect to web8 and db1:
ssh FTP_user@web18.3essentials.com -L 1434:db1.3essentials.com:1433 -N
*******************************************
Connection Successfull !
3Essentials hosting SSH Gateway.
Only authorized users are allowed.
All activity is logged.
Attempting to hack this server or
perform other illegal activities will
result in notification of your ISP.
*******************************************FTP_user@web18.3essentials.com's password:*******
To check if the connection has been successfully established, issue the following command:
telnet localhost 1434
To start the program in the background, use a -f switch.
ssh -f FTP_user@webYY.3essentials.com -L 1434:dbX.3essentials.com:1433 -N
- On a MAC, use that tunnel with a program called Aqua Data Studio to connect.
- SQL Server Management Studio 2008 connecting to SQL Server 2005:
- Using SQL Server Management Studio 2008 to connect to our SQL Server 2005, you will encounter an error when connecting indicating you do not have permissions to connect to a database, with that database not being your database. This problem is documented at the following URL, including a workaround:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=354322&wa=wsignin1.0
- The workaround is:
1. Navigate to 'Databases' in the database Object Explorer and open OED in that context
2. Right click on the column header and unselect 'Collation'
3. Refresh the server in Object Explorer and enumerate the databases
|
KB
Back to Search
