Data Source definition question when using SQL Server

[PHRED-SE]

I am trying to setup a data source connection on a SQL Server database that is on a server that is on a different network from the one that my ColdFusion 8 system is running on. 

It seems like this is a simple, straight forward thing: define the CD Data Source Name: MyTestData, Database: TestData, Server: 123.45.78.145\TestServer, User Name: my Name, Password: *****.  See my attached jpg for more detail.  Note: the IP address is bogus so don't fixate on that.

I can remotely log into that server using Terminal Server and bring up the SQL Server database so I know that both the IP address and the SQL Server names are correct.  I know that both the User Name and Password are correct so this leaves on the Server format. 

My question is what is the correct Server naming convention/format if this is not the correct format???

Thanks in advance for your help in this matter.

Len

[ilssac]

No picture yet, but two common things to double check.

First I have never used a IP/Name like that in my database field.  I have either used the IP OR server name in that field.  I.E. either something like '123.45.68.89' OR 'OurSQLServer'.

Second, and you will need to search for the exact terms for this, If you are using the latest SQL server -- 2008 I beleive? -- by default it does not allow the authentication protocol needed by ColdFusion.  We do not use 2008 here so I don't deal with this yet, but I have read many times that one needs to configure the SQL server to use some else -- like Windows TCP/IP? -- authentication rather then the default -- database? -- authentication.

[PHRED-SE]

Ian,

     Thanks for you reply.  I've tried it with just the IP address as you suggested, but that did not work either.  I know from past expereience that if you aren't on the same network, then you have to list the IP address because that is the only way that CF can locate the database server.  When doing do, I had also used the Database name along with the IP address.

     I should have mentioned that we are using SQL Server 2005 on both the local server and the remote one..

:-}

Len

[ilssac]

Then I can only share my DSN settings (somewhat modified to protected the innocent )  This is a DSN on my local developement box that connects to my production MSSql 2005 database located at my hosting provider.

CF Data Source Name: SOR

Database: SierraOutdoorRecreation

Server: SQL01.someserver.com

Port: 1433

UserName: NotTellingYou

Password: IamNotSaying

I presume replacing sql01.someserver.com with an IP would work fine, but as my hosting provider will move servers around as required the name stays the same while IP does not so this is the better version for us.

Any chance your database is not listening on the standard port?

[Scott_thornton]

HI,

I think the format you used originally refers to a named instance of sql server

eg: servername\instancename

[PHRED-SE]

Thanks to everyone who pitched in on this.

Scott expressed the correct answer very well.  Thanks.

The right answser is that the original server setup was pointing at a SQL Server 2000 instance on a server that also had a SQL Server 2005 instance on it. The server had been replaced with a new one on which there is only single instance: SQL Server 2005.  At the time I created this query, I didn't know that this switch had taken place.

When I went to add a new PORT value, all that was necessary was to enter either the IP adderess, 192.168.2.200 or the SQL Server name if it is visible to the CFServer.  In my case, the SQL Server name didn't make it to my side of the WAN so I had to use the IP address: 192.168.2.200.  I suppose I could have used the static IP address of this server, or www.somewebname.com.

Thanks to everyone who has shared their thoughts.  I solved the problem and forgot to finish off this open query. Sorry.

Len

aka, PHRED-SE.