0

What's the proper way to configure SQL Server Express 2005 so that it can allow for a number of clients to get connected to the server? I have my application running both in the server machine and the client machines. Given the nature of my application, clients are the branches geographically distant from each other, and the server itself. Every operation the client records must be reported to the server, because the server needs total control over the usage and production. But, what should I consider when configuring the connection in both sides, the server and the client? I'm not as used to SQL Server, I'm a beginner, however through SQL Server Configuration Manager I have set the main options without success. The problem seems to be related to trusted connections even though I have set it to support both windows and SQL Server authentication. When the client tries to connect to the server using windows authentication it displays no table; when it tries to communicate using a password (SQL Server authentication), tables are successfully displayed but no access is allowed...

Thanx in advance!

Improve this question

1 Answer 1

Reset to default
1

First, I'll use Windows Authentication only all your users are members of the same Active Directory domain, if not I'll use SQL Server Authentication (it's less secure but works better when not using a domain).

Then, make sure that SQL Server is configured to accept external connections. Open the Surface Area Configuration, go to configuration for services and connections, and in Database Engine > Remote connections check that Local and Remote connections is selected (usually you only need to enable TCP/IP connections).

The rest of requisites varies depending on how you installed it:

  • If you changed the installations options to use the default instance (the same configuration that the Standard/Enterprise versions use by default) you need to have port 1433 TCP opened in the firewall (unless you changed the default port).

  • If you used the default installation options SQL Server Express won't use the default instance, but instead it uses servername\SqlExpress. This requires having the SQL Browser service enabled and the port 1434 UDP opened in the firewall. Also you need to specify servername\SqlExpress or ip_number\SqlExpress when configuring the connection in the clients instead of just using the server name or IP.

    Note that AFAIK you can change only the instance at installation time, if you want to use the default instance you need to uninstall and install it again.

Finally, if you use queries that involve accessing data from different servers at the same time (i.e. you have different data in different servers), in the Surface Area Configuration go to the configuration for features and in Ad Hoc Remote Queries check Enable OPENROWSET and OPENDATASOURCE support.

Improve this answer
2
  • In my experience port 1433 is the only port I need to open in the firewall (rather than 1434) but my clients are all using TCP jdbc drivers.
    – djangofan
    Feb 14, 2012 at 21:29
  • Port UDP 1434/SQLBrowser service are sometimes required when you don't use the default instance, which is the default setting for Express editions (for that reason I never use the default installation options of Express editions). For Standard and Enterprise editions the default setting is using the default instance and disabling SQL Browser, so you only need TCP 1433 (unless you change the settings or use more that one instance at the same time).
    – Alberto Martinez
    Mar 11, 2012 at 15:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .