• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 922
  • Last Modified:

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


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
chokka
Asked:
chokka
  • 14
  • 5
  • 3
1 Solution
 
cyberkiwiCommented:
Don't include the port number

202.248.167.186\SQLEXPRESS
0
 
cyberkiwiCommented:
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
 
AmmarRCommented:
in Sql you dont enter colon between ipaddress and port

you should keep a coma

 202.248.167.186,2546
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
chokkaStudentAuthor Commented:


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
 
chokkaStudentAuthor Commented:
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
 
chokkaStudentAuthor Commented:
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
 
chokkaStudentAuthor Commented:
Do we need to perform some Network Enable at Server where SQL 2005 Express Edition database is installed ?
0
 
chokkaStudentAuthor Commented:
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
 
cyberkiwiCommented:
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
 
AmmarRCommented:
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
 
chokkaStudentAuthor Commented:
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
 
chokkaStudentAuthor Commented:
I can share the username & password for you. if you want to try from your end.
0
 
chokkaStudentAuthor Commented:
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
 
chokkaStudentAuthor Commented:
I have created username / password


Server Name : 209.248.167.186,2546\SQLEXPRESS

Static Port # 2546

chokka / chokka

0
 
chokkaStudentAuthor Commented:
Can any one help me .. or want to enter into my server computer remotedly... i can do that ...!
0
 
AmmarRCommented:
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
 
AmmarRCommented:
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
 
chokkaStudentAuthor Commented:
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
 
AmmarRCommented:
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
 
chokkaStudentAuthor Commented:

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
 
chokkaStudentAuthor Commented:
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
 
chokkaStudentAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 14
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now