SQL connection string

Question

website needs to go live which i have placed on client server.i have gven all permissions for IIS.in web.config file i have also given SQL connection string as :

<appSettings>
<add key="ConnectionString" value="Data Source=USER\SQLEXPRESS;Initial Catalog=people_metrix;User ID=MyUser;Password=abc123"/>  </appSettings>

In sql server 2005 have allowed remote connections(TCP/IP and named pipes).
but am not able to login to webasite.Username and password exist in database.

In server,firewall is on and there is no IP address(Obtains IP adress automatically).Is Login not happening due to these reasons ?

Answer 1

Please don't EVER use 'sa' in a connection string. EVER!

First try connecting using SQL Server Management studio with your connection string credentials (and not sa!).

Does the firewall on SQl sServer machine allow traffic on port 1433?

To allow connections to SQL Server by TCP port, perform the following steps.

1. Click Start | Control Panel | Network Connections
2. Right click on the network adapter that services inbound connections to SQL Server and select Properties
3. Select Advanced | Windows Firewall | Settings
4. Select Exceptions | Add Port
5. Under Name put "SQL Server" and under Port Number select 1433. Use TCP as the protocol.
6. Click OK to close out all dialogs.

If you're using anything other than standard TCP/IP to access SQL Server, you'll need to enable different ports. To allow RPC over Named Pipes instead of TCP, open port 445 in the same way that I described above.

See this MS support article for configuring remote connections: How to configure SQL Server 2005 to allow remote connections

Answer 2

You might check out connectionstrings.com

It has several connection string examples.