Solved

How to use Static IP Address as Servername for SQL Express Database.

Posted on 2010-09-19
22
849 Views
Last Modified: 2012-05-10

VS 2005 / SQL 2005 Express

We have a Windows Application running on using SQL 2005 Express Database.

Issue :-

We want to run the application outside our Workgroup by connecting the database through Static IP Address

At present our connection string are mentioned as COMPUTERNAME\SQLEXPRESS

On using Static Ip Address ( 202.248.167.186:2546 ), I am trying to connect as ..

202.248.167.186:2546\SQLEXPRESS

I am not able to suceed. How to use Static IP Address as local servername in SQL Server 2005 or SQL 2005 Express



0
Comment
Question by:chokka
  • 14
  • 5
  • 3
22 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33712859
Don't include the port number

202.248.167.186\SQLEXPRESS
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33712884
Since you are using a nonstandard port, have a read if this.
http://www.databasejournal.com/features/mssql/article.php/3689846/Using-Non-Standard-Port-for-SQL-Server.htm

Make sure you have set up the firewall (client/server) for the port 2546
On the client, you can set up an alias using Start->Run->CliConfg.exe or the Configuration Manager from the link above.

If using CliConfg, go to tab 2 "Alias", click Add, enter these parameters:
Server alias: 202.248.167.186\SQLEXPRESS
Network libraries: TCP/IP
Server Name: 202.248.167.186\SQLEXPRESS
Port: 2546
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33713844
in Sql you dont enter colon between ipaddress and port

you should keep a coma

 202.248.167.186,2546
0
 

Author Comment

by:chokka
ID: 33716403


CyberKiwi :- I followed the steps mentioned by you ..

Server alias: 202.248.167.186\SQLEXPRESS
Network libraries: TCP/IP
Server Name: 202.248.167.186\SQLEXPRESS
Port: 2546


While connecting from SQL Server Management Studio to the Database ..
1) I tried both 202.248.167.186 and 202.248.167.186,2546

But i am facing this error



Error.JPG
0
 

Author Comment

by:chokka
ID: 33717037
Now i am getting a different message

TITLE: Connect to Server
------------------------------

Cannot connect to 209.248.167.186\SQLEXPRESS.

------------------------------
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
0
 

Author Comment

by:chokka
ID: 33717117
Now,I am using the right IP Address

209.248.167.186:2546

Earlier, i gave wrong IP Address...!

But i am getting the following error

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)
0
 

Author Comment

by:chokka
ID: 33719200
Do we need to perform some Network Enable at Server where SQL 2005 Express Edition database is installed ?
0
 

Author Comment

by:chokka
ID: 33719544
TITLE: Connect to Server
------------------------------

Cannot connect to 209.248.167.186\SQLEXPRESS,2546.

------------------------------
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33721698
It doesn't sound like surface area configuration, but try this anyway: http://support.microsoft.com/kb/914277

Otherwise, try checking firewalls on both sides again.
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33722540
dear chokka

you are from the message you mentioned above
you are trying to connect to

Cannot connect to 209.248.167.186\SQLEXPRESS,2546.

it should be

Cannot connect to 209.248.167.186,2546\SQLEXPRESS

try this
0
 

Author Comment

by:chokka
ID: 33726353
Dear Ammar,

Either you try this way

209.248.167.186,2546\SQLEXPRESS

or
209.248.167.186\SQLEXPRESS,2546

or

209.248.167.186\SQLEXPRESS

I am getting the same error. And i think all the above are correct.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:chokka
ID: 33726358
I can share the username & password for you. if you want to try from your end.
0
 

Author Comment

by:chokka
ID: 33726570
Also server, which iam trying to connect remotely has more than one version of SQL

It has SQL 2005 Express, SQL 2008, etc.. ( Remaining i am not sure what versions .., Because in Services.MSC -> it shows more than 2 SQL Instances
0
 

Author Comment

by:chokka
ID: 33726743
I have created username / password


Server Name : 209.248.167.186,2546\SQLEXPRESS

Static Port # 2546

chokka / chokka

0
 

Author Comment

by:chokka
ID: 33726753
Can any one help me .. or want to enter into my server computer remotedly... i can do that ...!
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33731770
Dear chokka:

i will try to login and get back to you soon.

but now am not home, i will be back later today.
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33731967
Dear chokka

i am trying to login, but getting error.

are you sure the server is online, i wasn't able to ping it, check the image attached.

by the way check these articles may be they help

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_1881-Connect-to-your-SQL-Server.html

http://support.microsoft.com/kb/914277

http://blogs.msdn.com/b/sql_protocols/archive/2005/12/22/506607.aspx

---

check out this site
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx

they have useful sql tools that will help you analyse the problem

i tried SQLPing with your server and still i didnt get any response

sql-server.png
0
 

Author Comment

by:chokka
ID: 33735489
Dear Ammar R,

Yes server is online.
I have a small website running on that server .. depending on SQL Server Express

http://209.248.167.186:2546/Marketing/login.aspx

if you can run this website, it means .. server is ONLINE.

I have attached a screenshot who  shows list of SQL Instances on the server machine.

I think SQL Server Agent is stopped. Its not on Automatic. But SQL Browser service is Automatic.

Please check and help me.




SQLAgent.JPG
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33741152
Dear chokka:

i looks like you have 3 Sql server instances

    instance name -- status --- user
1. SQL Server (ACT7)   -- disabled -- local system
2. SQL Server (ELVISSQLEXPRESS) -- started-- network service
3. SQL Server (SQLSEXPRESS) -- started-- local system

Sql agent and sql browser are not a big deal, i mean you can work even if they are stopped

now if you are sure that you website is running on SQL Server (SQLSEXPRESS), why dont you check the connection string of the website (by default connection string is stored in web.config file), how is the website connected to your server.
and login to it using SSMS

if you can download and run TCPView
http://technet.microsoft.com/en-us/sysinternals/bb897437.aspx

it will show you the running processes you have and on which port, look at the image attached below, showing my default sql instance running on 1433 (default port), you need to check yours

or
you can check that by going to SQL server Configuration manager (from start menu --> sql server --> configuration tools) and see your SQLSEXPRESS on which port is it running

because usually named instances are given a random port you need to know it.

sqlserver-port.png
0
 

Author Comment

by:chokka
ID: 33744454

On running Tcpview.exe, i received the following details about SQL...

***************************************************************************************************************


sqlbrowser.exe      2176      UDP      Elvis      ms-sql-m      *      *                                                                  
sqlbrowser.exe      2176      UDPV6      elvis      1434      *      *                                                                  
sqlservr.exe      1940      TCP      Elvis      49157      Elvis      0      LISTENING                                                            
sqlservr.exe      1512      TCP      Elvis      49158      Elvis      0      LISTENING                                                            
sqlservr.exe      1940      TCPV6      elvis      49157      elvis      0      LISTENING                                                            
sqlservr.exe      1512      TCPV6      elvis      49158      elvis      0      LISTENING
0
 

Author Comment

by:chokka
ID: 33744554
Okay, i have attached the Port Details of the website

http://209.248.167.186:2546/Marketing/login.aspx

Which is depending on this server database.
PortDetails.jpg
0
 

Author Closing Comment

by:chokka
ID: 33804509
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now