none
how to access to a remote SQL server database?

    Question

  • hi everybody,

    I'm really a beginner with database and I don't know how can I access by example with SQL server management studio (express or not) to a remote sql database which is based on a remote server through internet.

    When I start SQL server management studio express I can put "name of the server" and authentication but I really don't know what to put here. Do I need to put a remote IP address? Does it work like that?

    Can i access to a remote database with SQL server management studio or I need something else?

    What I want to do is to begin, access to this database, and then, I would like to copy a table from this database to a local SQL server.

    Any help will be really appreciated!

    Thanks

     

    Sunday, September 05, 2010 10:21 AM

Answers


  • 1) Do you think the express version is sufficient to do that? Yes

    So I tried to choose SQL Server Authentication as you have said. Then I have to give connection and password. What Do I need to put in Connection field?   My SQL server is partner hosted so I just need to know how to access data and I ask to my partner to give me the right parameters.  you can use <IPaddress>\instancename

    2) Thanks for the import/export wizard link. In fact, I need to update periodically a table in a SQL server with a table in "remote domain Outside SQL server". Could i do that with the export import wizard because I need with this tool I could do that only once?  In express edition ther is no SQL Server agent that mean you can not have a scheduled job running.

    Thanks a lot.

    • Marked as answer by Ai-hua Qiu Monday, September 13, 2010 7:31 AM
    Sunday, September 05, 2010 3:47 PM

All replies

  • >>When I start SQL server management studio express I can put "name of the server" and authentication but I really don't >>know what to put here

    First of all when you same remote do you mean SQL server is on the same network domain or outside?

    If you are trying to connect to a sql server from a computer which is in the same network domain then you can use hostname or IPAdress of the remote sql server. If you are connecting to a SQL server outside your domain then you can use SQL server authentication.

    >>Can i access to a remote database with SQL server management studio or I need something else?

    You do not need anything else but make sure

    1) remote connections are allowed and enable TCP/IP using sql server configuration manager (to launch sql server configuration manager Start-->Programs--Microsoft SQL Server--> Configuration Manager)

    2) If you are connecting to a named instance then SQL Server browser service is running and in Windows Firewall Add Exception to UDP port 1434

    Please see step by step...(info is still valid for other version of  SQL server )

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

    to add firewall exception

    http://www.mssqltips.com/tip.asp?tip=1929

    >>What I want to do is to begin, access to this database, and then, I would like to copy a table from this database to a local SQL server.

    You can use import export wizard to copy table see link below

    http://msdn.microsoft.com/en-us/library/ms141209.aspx

     

    • Edited by Chirag Shah Sunday, September 05, 2010 12:35 PM edit
    Sunday, September 05, 2010 12:27 PM
  • Hi chirag Shah and thanks.

    1) I meant My SQL server is outside the domain. I'm trying to access this server and this database with MS SQL management studio express. Do you think the express version is sufficient to do that?

    So I tried to choose SQL Server Authentication as you have said. Then I have to give connection and password. What Do I need to put in Connection field?

    My SQL server is partner hosted so I just need to know how to access data and I ask to my partner to give me the right parameters.

    2) Thanks for the import/export wizard link. In fact, I need to update periodically a table in a SQL server with a table in "remote domain Outside SQL server". Could i do that with the export import wizard because I need with this tool I could do that only once?

    Thanks a lot.

    Sunday, September 05, 2010 3:10 PM

  • 1) Do you think the express version is sufficient to do that? Yes

    So I tried to choose SQL Server Authentication as you have said. Then I have to give connection and password. What Do I need to put in Connection field?   My SQL server is partner hosted so I just need to know how to access data and I ask to my partner to give me the right parameters.  you can use <IPaddress>\instancename

    2) Thanks for the import/export wizard link. In fact, I need to update periodically a table in a SQL server with a table in "remote domain Outside SQL server". Could i do that with the export import wizard because I need with this tool I could do that only once?  In express edition ther is no SQL Server agent that mean you can not have a scheduled job running.

    Thanks a lot.

    • Marked as answer by Ai-hua Qiu Monday, September 13, 2010 7:31 AM
    Sunday, September 05, 2010 3:47 PM
  • 1) Which password do I need to put? Because I have just to specify <IPaddress>\instancename and password, but no login or other. Is it a password of a user? and if yes, which user?

    2) Ok, so I will user SQL Server management studio with Server agent. Do you know how to use "Server Agent" to have scheduled job running?

    Thanks!

    Sunday, September 05, 2010 4:13 PM
  • Hi, try in the SQL Management Studio in the connection screen to put the following information:

    Server type: Database Engine

    Server name: <IPAdress>\SQLEXPRESS

    Authentication: SQL Server Authentication (if you AD user doesn't have access set up in the server and if it accept SQL Server Authentication too)

    Login: <Login to the server>

    Password: <Your Password>

    There are many ways to update your table, the most visual you can do is using Integration Services, but for that you'd have to have full SQL Server in one of your servers. If it is not the case, you can also use triggers to control the data transport but it is more difficult. Maybe there are other choices, but those are the ones i use.

    Sunday, September 05, 2010 11:44 PM