I have a big trouble with connecting a SQL server database...
The code for connecting SQL server database I used to connect to other SQL server database is as following:
Dim cnReg As SqlConnection
cnReg = New SqlConnection(ConfigurationSettings.AppSettings("MyConnection"))
Dim dsReg As New DataSet
Dim comReg As New SqlCommand
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = cnReg
Dim daReg As New SqlDataAdapter(comReg)
In Web.config file
<add key="MyConnection" value="Persist Security Info=False;Data Source=18.104.22.168;User ID=myid; Password=mypassword;Initial Catalog=mydb;" />
The above code works correctly for my other projects. For my current project, since this hosting company doesn't allow external access SQL server database, I need to use server name, instead of server IP address, for the Data Source, so I change the setting in web.config as following:
<add key="MyConnection" value="Persist Security Info=False;Data Source=whsql-v09.prod.mesa1.secureserver.net;User ID=myid; Password=mypassword;Initial Catalog=mydb;" />
When I try to run the application, I got the error:
System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
I am using .NET Framework 1.1 and have SQL server service manager installed on my testing machine. Also, have McAfee personal Firewall installed.
I think, it's probably because there is something wrong with the setting on my local machine. I have spent the whole day trying to find out the problem, but still couldn't solve it...
Can anybody give me some ideas about how to solve the problem? Any advices and help would be very appreciated!
-- modified at 20:16 Tuesday 25th April, 2006