connect to server via intranet - how is it done (new to this)

Hi all!

I have msde2000 on one machine. want to connect to it from other machine. both machines are connected via a switch with integrated firewall.

Do I need to set up this firewall to enable connections? Like opening ports for msde in the virtual servers settings of the firewall?

If I do this, will that make my sql-server visible and accessible to the internet?

Or should I be able to find my sql server without such steps. And if so how?

networking is up and i can access folders of both machines via the intranet. Is that enough or do i need more settings here?

Let's say the machine and thus the server is called Alpha. What goes into my connectionstring from the other machine?

this wont be enough, right?
strconn = "Provider=SQLOLEDB;" & _
"Data Source="Alpha";"

Please shed some light on these questions.

tia

KumaFirst you will have to configure the ODBC settings in your machine. Make sure the datasource name is "alpha" , the same as the server name. You can then use "alpha" in the connection string. The code that is required is

'VB Code starts here

Set db = New ADODB.Connection

db.Properties("Data Source").Value = "alpha"
db.Properties("Initial Catalog").Value = "Northwind"
db.Properties("User ID").Value ="XXX"
db.Properties("Password").Value = "YYYYY"
db.Open

'ASP Code starts here
Set db = Server.CreateObject("ADODB.Connection")
db.Open "Provider=SQLOLEDB;Data Source=alpha;Initial Catalog=Northwind;User Id=XXX;Password=YYY"

'CODE ENDS HERE

Roshmi Choudhury

Configuring Reporting Services behind a firewall

Hi,

I put my SQL server behind the firewall, and I have put SQL_PORT and “Reporting Services Configuration” in the exception list.

However, on a remote machine, if I use SQL Reporting Services Configuration Manager to change the Database Connection setting, I will get an error in the “Create a Grants Rights Script” stage.

Looking at the exception, it points to an RPC problem.

System.Runtime.InteropServices.COMException (0x800706BA): The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)

at System.Management.ManagementScope.InitializeGuts(Object o)

at System.Management.ManagementScope.Initialize()

at System.Management.ManagementObjectSearcher.Initialize()

at System.Management.ManagementObjectSearcher.Get()

at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses(String machine)

at ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String dbServer)

at ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String connectionString, String server, String database, ConfigurationCredentialsType credsType, String account, String password, Boolean upgrade, String dbVersion)

Does it mean that I have to enable ALL RPC ports in order to configure RS behind a firewall?

Thanks,

Patrick Ng

Did Anybody found a solution for this..

I'm also facing the same problem...

Thanks

|||

The Configuration Manager uses WMI in some communication with the SQL Server catalog machine. You will need to ensure your firewall allows DCOM traffic between the two boxes while you are setting them up. The following article suggests protecting communication between the boxes using IPsec:

http://msdn2.microsoft.com/en-us/library/ms159272.aspx

Thanks, Donovan.

|||

Just to clarify - In the firewalled scenario, typically you are trying to allow users from outside the firewall to view/access reports. To do this they need URL based access (typically port 80) on to the report server. These users do not need acess to WMI - it is only used for administrative reasons (like creating the report server database). You should not expose the WMI provider (DCOM) outside your firewall. The operation you’re trying to do is an administrative operation that should be carried out inside the safety of your network either locally on the computer or through a secure channel inside your network. Exposing DCOM to the broader network significantly increases your security exposure and should not be done unless you have really good reason for doing it and consider ahead of time the implications.

Hope that helps,

-Lukasz

|||From what I could tell DCOM runs on TCP 135. When I opened this port between my reporting server and my sql server, it still gives the same error. Are there additional ports that you're aware of, or am I wrong altogether?|||Open your firewall log and see if you can determine which port is being used but is blocked.

Configuring Reporting Services behind a firewall

Hi,

I put my SQL server behind the firewall, and I have put SQL_PORT and “Reporting Services Configuration” in the exception list.

However, on a remote machine, if I use SQL Reporting Services Configuration Manager to change the Database Connection setting, I will get an error in the “Create a Grants Rights Script” stage.

Looking at the exception, it points to an RPC problem.

System.Runtime.InteropServices.COMException (0x800706BA): The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)

at System.Management.ManagementScope.InitializeGuts(Object o)

at System.Management.ManagementScope.Initialize()

at System.Management.ManagementObjectSearcher.Initialize()

at System.Management.ManagementObjectSearcher.Get()

at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses(String machine)

at ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String dbServer)

at ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String connectionString, String server, String database, ConfigurationCredentialsType credsType, String account, String password, Boolean upgrade, String dbVersion)

Does it mean that I have to enable ALL RPC ports in order to configure RS behind a firewall?

Thanks,

Patrick Ng

Did Anybody found a solution for this..

I'm also facing the same problem...

Thanks

|||

The Configuration Manager uses WMI in some communication with the SQL Server catalog machine. You will need to ensure your firewall allows DCOM traffic between the two boxes while you are setting them up. The following article suggests protecting communication between the boxes using IPsec:

http://msdn2.microsoft.com/en-us/library/ms159272.aspx

Thanks, Donovan.

|||

Just to clarify - In the firewalled scenario, typically you are trying to allow users from outside the firewall to view/access reports. To do this they need URL based access (typically port 80) on to the report server. These users do not need acess to WMI - it is only used for administrative reasons (like creating the report server database). You should not expose the WMI provider (DCOM) outside your firewall. The operation you’re trying to do is an administrative operation that should be carried out inside the safety of your network either locally on the computer or through a secure channel inside your network. Exposing DCOM to the broader network significantly increases your security exposure and should not be done unless you have really good reason for doing it and consider ahead of time the implications.

Hope that helps,

-Lukasz

|||From what I could tell DCOM runs on TCP 135. When I opened this port between my reporting server and my sql server, it still gives the same error. Are there additional ports that you're aware of, or am I wrong altogether?
|||Open your firewall log and see if you can determine which port is being used but is blocked.

Configuring Reporting Services behind a firewall

Hi,

I put my SQL server behind the firewall, and I have put SQL_PORT and “Reporting Services Configuration” in the exception list.

However, on a remote machine, if I use SQL Reporting Services Configuration Manager to change the Database Connection setting, I will get an error in the “Create a Grants Rights Script” stage.

Looking at the exception, it points to an RPC problem.

System.Runtime.InteropServices.COMException (0x800706BA): The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)

at System.Management.ManagementScope.InitializeGuts(Object o)

at System.Management.ManagementScope.Initialize()

at System.Management.ManagementObjectSearcher.Initialize()

at System.Management.ManagementObjectSearcher.Get()

at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses(String machine)

at ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String dbServer)

at ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String connectionString, String server, String database, ConfigurationCredentialsType credsType, String account, String password, Boolean upgrade, String dbVersion)

Does it mean that I have to enable ALL RPC ports in order to configure RS behind a firewall?

Thanks,

Patrick Ng

Did Anybody found a solution for this..

I'm also facing the same problem...

Thanks

|||

The Configuration Manager uses WMI in some communication with the SQL Server catalog machine. You will need to ensure your firewall allows DCOM traffic between the two boxes while you are setting them up. The following article suggests protecting communication between the boxes using IPsec:

http://msdn2.microsoft.com/en-us/library/ms159272.aspx

Thanks, Donovan.

|||

Just to clarify - In the firewalled scenario, typically you are trying to allow users from outside the firewall to view/access reports. To do this they need URL based access (typically port 80) on to the report server. These users do not need acess to WMI - it is only used for administrative reasons (like creating the report server database). You should not expose the WMI provider (DCOM) outside your firewall. The operation you’re trying to do is an administrative operation that should be carried out inside the safety of your network either locally on the computer or through a secure channel inside your network. Exposing DCOM to the broader network significantly increases your security exposure and should not be done unless you have really good reason for doing it and consider ahead of time the implications.

Hope that helps,

-Lukasz

|||From what I could tell DCOM runs on TCP 135. When I opened this port between my reporting server and my sql server, it still gives the same error. Are there additional ports that you're aware of, or am I wrong altogether?
|||Open your firewall log and see if you can determine which port is being used but is blocked.

Configuring Reporting Services behind a firewall

Hi,

I put my SQL server behind the firewall, and I have put SQL_PORT and “Reporting Services Configuration” in the exception list.

However, on a remote machine, if I use SQL Reporting Services Configuration Manager to change the Database Connection setting, I will get an error in the “Create a Grants Rights Script” stage.

Looking at the exception, it points to an RPC problem.

System.Runtime.InteropServices.COMException (0x800706BA): The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)

at System.Management.ManagementScope.InitializeGuts(Object o)

at System.Management.ManagementScope.Initialize()

at System.Management.ManagementObjectSearcher.Initialize()

at System.Management.ManagementObjectSearcher.Get()

at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses(String machine)

at ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String dbServer)

at ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String connectionString, String server, String database, ConfigurationCredentialsType credsType, String account, String password, Boolean upgrade, String dbVersion)

Does it mean that I have to enable ALL RPC ports in order to configure RS behind a firewall?

Thanks,

Patrick Ng

Did Anybody found a solution for this..

I'm also facing the same problem...

Thanks

|||

The Configuration Manager uses WMI in some communication with the SQL Server catalog machine. You will need to ensure your firewall allows DCOM traffic between the two boxes while you are setting them up. The following article suggests protecting communication between the boxes using IPsec:

http://msdn2.microsoft.com/en-us/library/ms159272.aspx

Thanks, Donovan.

|||

Just to clarify - In the firewalled scenario, typically you are trying to allow users from outside the firewall to view/access reports. To do this they need URL based access (typically port 80) on to the report server. These users do not need acess to WMI - it is only used for administrative reasons (like creating the report server database). You should not expose the WMI provider (DCOM) outside your firewall. The operation you’re trying to do is an administrative operation that should be carried out inside the safety of your network either locally on the computer or through a secure channel inside your network. Exposing DCOM to the broader network significantly increases your security exposure and should not be done unless you have really good reason for doing it and consider ahead of time the implications.

Hope that helps,

-Lukasz

|||From what I could tell DCOM runs on TCP 135. When I opened this port between my reporting server and my sql server, it still gives the same error. Are there additional ports that you're aware of, or am I wrong altogether?|||Open your firewall log and see if you can determine which port is being used but is blocked.

configure SQL Server Express with Windows Firewall

I have changed SQL Server port to 2433, and add it to exception in Windows Firewall, add executive files as in this KB http://msdn2.microsoft.com/en-us/library/ms175043.aspx

Want to change port of SQL Server Browser as well, but dont know how to :(

anyway, after enable Firewall, SQL server is stop working. How to get it working with Firewall? Also, if some one lets me know how to change port of SQL Server Browser too, it would be great

thanks

As far as I know, you can not change the port that is used by SQL Browser. Based on this BOL topic, it seem that port has to remain the same.

I'd need more information to answer the other question. What do you mean that SQL Server stops working? If you've changed the port that SQL is using, you will need to specify the exact port number in your connection, are you doing that?

Regards,

Mike Wachal
SQL Express team

Mark the best posts as Answers!

|||

You cannot change the UDP port number (1434) for SQL browser. The clients will always connect to this port to obtain SQL Server configuration.

In your case, you need to put UDP 1434 or SQL Browser into your firewall exception.