Connecting to Remote SQL Server database

[karmaahost]

We have Power 2200 series server with windows 2003 standard and SQL 2000 database. How do we allow our users to connect to their individual databases without breaching security from local version of SQL enterprise manager?

Any help would be greatly appreciated.

[DXD]

You need to first have a Microsoft SPLA agreement to be properly licensed to allow your customers to connect to their sql databases.

Then your need to setup your SQL instance to listen on a different port. The standard SQL ports are blocked by ServerBeach for security reasons.

Once you have it listen on another port your customers can connect to SQL server with enterprise manager from their machine.

[karmaahost]

Hi Chris,

>>You need to first have a Microsoft SPLA agreement to be properly licensed to allow your customers to connect to their sql databases.
How do we proceed with this?

>>Then your need to setup your SQL instance to listen on a different port. The standard SQL ports are blocked by ServerBeach for security reasons.
How do we have SQL instance listen to a different port other than 1433?

[GaryK]

Microsoft SPLA (http://www.microsoft.com/serviceproviders/licensing/default.mspx).

To change ports:
1. Startup SQL Server Client Network Utility
2. Select the Alias tab
3. Click on Add... to add a server alias configuration
4. Set your Server Alias to <MyAliasToDB>
5. Choose TCP/IP Network Libraries
6. For connection parameters, enter:
* Server Name: <sql server ip>
* Dynamically determine port unchecked
* port:<sql server port>
7. Select OK on the Network Library Configuration Window
8. Select OK on the SQL Server Client Network Utility Window
9. From the Enterprise Manager Application:
10. Select New SQL Server Registration
11. You should see <MyAliasToDB> in the Available servers list

Please take all availabe security precations because what you're doing is potentially very risky. That's why SB has blocked the default remote port. ;)

[karmaahost]

Hi Gary,

We are trying with your steps but just have quick question. Should we put the port as 1433 or something else such as 3305.

What are the security precautions that you are referring to? Could you please specify.

Thanks for your help and tips

[flc]

We are trying with your steps but just have quick question. Should we put the port as 1433 or something else such as 3305.
I believe Server Beach is still blocking port 1433 so you should try another port (such as 3305) until you find one that is not blocked.
What are the security precautions that you are referring to? Could you please specify.
You can start with a strong sa password. Then be sure to install all service packs.

[karmaahost]

We tried port 3305 but failed. How do we find out which ports are open so that we could have our clients use them to connect to their database?

Will this allow the access to only their database or they will be able to see all databases on the server?

[DXD]

There is not a list of available ports you just need to try different ones.

try 1434 or 1435


As for your customers they will see ALL databases but will only be able to access the ones you have given them permission to.

This is by design and as far as I know there is no way around it.

[KyleMulligan]

As for your customers they will see ALL databases but will only be able to access the ones you have given them permission to.

This is by design and as far as I know there is no way around it.
http://support.microsoft.com/default.aspx/kb/889696

[DXD]

Kyle is the MAN! :bow:

[karmaahost]

We tried to use 1435,1434,3305 but didnot connect. What are we missing here. We even created an alias for the database from Client network utility but it still does not connect.

Can anybody suggest an open port which can be used for these for database connectivity for our client?

[DXD]

1435 should work that's whatI use.

[KyleMulligan]

Indeed. 1435 is what I used to use as well.

[karmaahost]

Should I create an alias with 1435 and then try to connect or will it detect this port?

Getting very close

[DXD]

I just use the port in Enterprise manager I don't create an alias.

servername.domain.com,port

ex: sql.myserver.com,1435

[flc]

http://support.microsoft.com/default.aspx/kb/889696
Thanks for digging up this link. For years we've been told that it could not be done.

One should note that although making the changes outlined in the article may only display the customer's databases, a determined user using TSQL or other tools may still be able to see the names of all the databases in the server.

This is a still a great solution.

[karmaahost]

Hi flc,

We tried ports 1434,1435,and 3055 just to find out that we cannot connect via SQL Enterprise Manager. How do we know which ports on the database server is listening?

Please let us know and we could use that as a last resource

[flc]

To find out the port being used go to Start/Run/SVRNETCN.EXE.
In the General tab make sure that TCP/IP is enabled. Select it from the Enabled protocols window and click on the Properties button. This should tell you the port your are listening to and give you an opportunity to change it.

You need to restart SQL for changes to take effect.

Then, as DXD indicated, you should use the server's URL or IP followed by a comma and the port to talk to it. Example: 127.0.0.1,1435

I hope this helps.

BTW - If you are running a firewall make sure you configure it for the new port.

[karmaahost]

We tried that but no luck. We have opened up a support ticket with ServerBeach to see if they could figure this one out.

I appreciate all your help from all of you.