Accessing via ODBC source and 127.0.0.1 not possible

Question

Accessing via ODBC source and 127.0.0.1 not possible

marin-231997

SSC Eights!

Points: 973
More actions
August 30, 2010 at 5:12 am

#228386

Hello all,
I wonder if somebody could point me in the right direction.
After a successful installation of SQL Server 2005 Express ([MY_COMPUTER_NAME]\SQLEXPRESS) I can not access it via ODBC source via 127.0.0.1\SQLEXPRESS.
I have verified that TCP/IP protocol is enabled for SQL Server instance and as a client protocol.
127.0.0.1 is pingable, localhost as well. OS is Windows 7.
It seems as if ODBC source to server is only possible by specifiying machine name instead of 127.0.0.1.
What could cause this behaviour?
Many thanks for any hints...
Marin

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think because the express edition was installed, and not a default instance, no service is there to answer a connection to 127.0.0.1.

try building a connection string to [127.0.0.1\SQLEXPRESS] instead. you can also connect to the specific listenning port with 127.0.0.1[comma][port]...on my machine, it's 1712...yours could be different.

as an example, on my machine, which is namned "D223", i have a default instance and an express instance;

these are the ways i can connection to either instance:

Default(SQL 2008)

D223

127.0.0.1

(local)

localhost

. --yeah, just a dot

--yeah, leaving it blank gets the default instance

192.168.1.40

127.0.0.1,1433

192.168.1.40,1433

Instance:SQLExpress(2005)

D223\SQLEXPRESS

127.0.0.1\SQLEXPRESS

(local)\SQLEXPRESS

localhost\SQLEXPRESS

.\SQLEXPRESS

192.168.1.40\SQLEXPRESS

127.0.0.1,1712

192.168.1.40,1712

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

marin-231997 SSC Eights! Points: 973 More actions · Answer 2

Hello Lowell,

thanks for the quick response. Unfortunately I can not connect via 127.0.0.1\SQLEXPRESS - this is my problem described in the issue. If I use machine name instead of 127.0.0.1 it is working fine.

I need the possiblity to connect via ODBC as 127.0.0.1\SQLEXPRESS. I really do not know where to dig...

Thanks,

Marin

marin-231997 SSC Eights! Points: 973 More actions · Answer 3

Hello Lowell,

thanks again for your response - in the meantime I managed to solve the problem myself.

It seems as if port 1433 is not automatically set on installation for all IP addresses in the network configuration (pic attached).

Once port is manually set in the indicated form, 127.0.0.1\SQLEXPRESS can be addressed via ODBC source.

Marin