| Connecting to MSSQL 2005 server through SQL Server 2005 Management Studio Express | Article ID:246 |
| Steps for connecting to MSSQL 2005 server through SQL Server Management Studio Express: 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 stored in db1.3essentials.com. . Connecting through Secure Enterprise Manager is a two step process. I. Establish a secure to connection II.Connect to the SQL server. I.There are two ways by which you secure your connection.(Use Only one) A. Using the tunnelier: 1Visit http://www.bitvise.com/download-area.html 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. 2. You need to create a tunnelier profile first for the MSSQL conenction 3.Open the Bitvise Tunnelier, On the main Window, in the space for the host type in your server address where your webspace 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). 4:For Authentication,use your ftp user name and password.(initial method should be password).Refer your Welcome e-mail for the credentials. 4.Port No should be 22, SSPI/Kerberos 5 should be checked. All other fields can be left blank 6Click on the tab called C2S Forwading,Make sure that you have the tabs as follows: Status->enabled, Listen Interface ->127.0.0.1, List.Port ->1434 ,Destination Port ->db1.3essentials.com,Dest Port ->1433, Comment->db1 7.Save the profile. Click on Login 8.Host Verification Window may popup, depending on version -Click or Accept and Save 9.User Authentication banner may popup, depending on version - close the banner (if you get this banner, you MUST close it for the tunnel connection to be made), 10.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 not able to create a profile for tunnelier,let us know,we'll create one and send it to you B:Using Plink 1. Download PLINK.EXE from the downloads section of this article 2. There's no install for plink, simply put it whereever you wish. If you put it into your windowssystem32 folder, you'll then be able to access it from anywhere without having to specify the path. 3. Open a command prompt on your local system (Click Start > Run > then type cmd > Click OK.) 4. Initialize the connection by running the following in the CMD prompt: plink.exe webX.3essentials.com and press Enter (webX is the server your site is hosted on, i.e., web14 or web16, etc.X=14) 5. If this is the first time you have connected using plink you will get prompted to accept the key, type y or yes and press . 6. Close the plink command window. (click the X in top right corner) That completes the initial setup. Next, you launch an SSH Tunnel from your system to ours: 1. Open a command prompt on your PC (Start>Run> type CMD). 2. In the CMD prompt execute the following command: plink.exe -ssh -l FTPUSER -pw FTPPASSWORD -L 1434:db1.3essentials.com:1433 -batch webX.3essentials.com -N (X=14) Note: a.replace FTPUSER with your ftp username 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 until you have the whole command in). d.NOTE: The -l is a lower case "L" 3. You should receive a successful connection message. Note: Leave the command window open, do not close the window. plink must be running and connected while you are using the connection. II.Connecting Through SQL server Management Studio Express: The SQL server Management Studio Express can be downloaded from the URL http://msdn.microsoft.com/vstudio/express/sql/download/ When you configure the MSSQL frontend(Enterprise Manager), make sure you use these values for the following parameters: Please note: 1. webX.3essentials.com is your webserver where your domain is physically hosted(X=14) 2. db1.3essentials.com is the MSSQL database server that holds the database that you've created from Plesk. A. Add Network Alias via SQL Server Configuration Manager: Add a server alias and enter the settings as shown below. Server Alias is a friendly name, and really can be anything you like. From your Windows System, Click Start > Programs > Microsoft SQL Server 2005 > 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' 1.Alias Name: db1.3essentials.com 2.Port number: 1434 3.Protocol .TCP/IP 4.Server : 127.0.0.1 B. Configure SQL Server Management Studio Express 1. In the Server name field enter the Alias (db1.3essentials.com) 2. Authentication type is SQL Server Authentication 3. Login: Enter the Database Username that you created in the PLESK control panel for your database 4. Password: Enter 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. GOTCHAS and other things that we've found can bite you with this particular configuration: 1) If you can't connect, Modify your ALIAS entry to the following: name: local port: 1434 (assuming you're using 1434 for this and not 1435 or another) protocol: tcp/ip server: 127.0.0.1 The use of "local" as the alias name is a quirk in SQL mgmt studio. 2) SQL Server Configuration Manager...for 64 bit OS, there are TWO options: SQL Native Client Configuration(32bit) SQL Native Client Configuration The second is presumably 64bit... you need to configure 32bit version, as SQL 2005 Mgmt Studio is 32bit. ######################### For Linux/Unix/Mac: The tunnel can be created on one line on linux/unix systems with the following ssh command: ssh FTP_user@webXX This will prompt for password, supply the FTP password to login. This is how it will look: 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, for instance, On a MAC, use that tunnel with a program called Aqua Data Studio to connect. |
|
| Downloads associated with this article: | |
| plink.zip : Plink SSH tunnel client |
|
 Back
to Search or Browse |
|