Databases
StackOverflow2013
Postsdbo
POEnable tcp\ip remote connections to sql server express already installed database with code or script(query)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEnable tcp\ip remote connections to sql server express already installed database with code or script(query)
    primarykey
    data
    text
    <p>I am deploying sql express with my application. I will like that database engine to accept remote connections. I know how to configure that manual by launching the sql server configuration manager, enabling tcp/ip connections, specifying the ports etc.. I am wondering if it will be possible to do the same thing from the command line. </p> <p>Or maybe I will have to create a "SQL Server 2008 Server Project" in visual studio. </p> <h2>EDIT 1</h2> <p><strong>I posted the same question in here but I will like to do the same thing on a instance of sql express that is already installed. <a href="https://stackoverflow.com/q/9137836/637142">Take a look at the question in here</a></strong></p> <h2>EDIT 2</h2> <p>I found these links that claim on doing something similar and I still cannot make it work.</p> <p>1) <a href="http://support.microsoft.com/kb/839980" rel="nofollow noreferrer">http://support.microsoft.com/kb/839980</a></p> <p>2) <a href="http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/c7d3c3af-2b1e-4273-afe9-0669dcb7bd02/" rel="nofollow noreferrer">http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/c7d3c3af-2b1e-4273-afe9-0669dcb7bd02/</a></p> <p>3) <a href="http://www.sql-questions.com/microsoft/SQL-Server/34211977/can-not-connect-to-sql-2008-express-on-same-lan.aspx" rel="nofollow noreferrer">http://www.sql-questions.com/microsoft/SQL-Server/34211977/can-not-connect-to-sql-2008-express-on-same-lan.aspx</a></p> <p>4) <a href="http://datazulu.com/blog/post/Enable_sql_server_tcp_via_script.aspx" rel="nofollow noreferrer">http://datazulu.com/blog/post/Enable_sql_server_tcp_via_script.aspx</a></p> <hr> <h2>EDIT 3</h2> <p>As Krzysztof stated in his response I need (plus other things that I know that are required)</p> <p><strong>1 - enable TCP/IP</strong></p> <p><img src="https://i.stack.imgur.com/CLsaK.png" alt="enter image description here"></p> <p>I have managed to do this when installing a new instance of SQLExpress passing the parameter <code>/TCPENABLED=1</code>. When I install sql express like in <a href="https://stackoverflow.com/a/9154540/637142">this example</a>. that instance of sql express will have TCP/IP enabled</p> <p><strong>2 - Open the right ports in the firewall</strong></p> <p>(I have done this manualy but I belive I will be able to figure it out how to do it with c#) So far I have to play aroud with this console command:</p> <pre><code>netsh firewall set portopening protocol = TCP port = 1433 name = SQLPort mode = ENABLE scope = SUBNET profile = CURRENT </code></pre> <p><strong>3 - Modify TCP/IP properties enable a IP address</strong></p> <p><img src="https://i.stack.imgur.com/lnDKM.png" alt="enter image description here"></p> <p>I have not been able to figure out how to enable a IP, change a port etc.. <strong>I think this will be the step more complicated to solve</strong></p> <p><strong>4 - Enable mixed mode authentication in sql server</strong></p> <p><img src="https://i.stack.imgur.com/IB9Wp.png" alt="enter image description here"></p> <p>I have managed to do this when installing SQL Express passing the parameter <code>/SECURITYMODE=SQL</code> refer to step 1's link. </p> <p>SQL Server express requires this authentication type to accept remote connections.</p> <p><strong>5 - Change user (sa) default passowrd</strong></p> <p>By default the sa account has a NULL passowrd. In order to accept connections this user must have a password. I changed the default passowrd of the sa with the script:</p> <pre><code>ALTER LOGIN [sa] WITH PASSWORD='*****newPassword****' </code></pre> <p><strong>6 - finally</strong></p> <p>will be able to connecto if all the last steps are satisied as:</p> <pre><code>SQLCMD -U sa -P newPassword -S 192.168.0.120\SQLEXPRESS,1433 </code></pre> <p>by typing that in the command line: the connection string in C# will be very similar. I will have to replace -U for user , -P for password and -S for data source. I dont recall the exact names.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USTono Nam
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POEnable tcp\ip remote connections to sql server express already installed database with code or script(query)
      1. USTono Nam
      1. VTBountyStart
    2. VO
      singulars
      1. POEnable tcp\ip remote connections to sql server express already installed database with code or script(query)
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POEnable tcp\ip remote connections to sql server express already installed database with code or script(query)
      1. USSergey Brunov
      1. VTFavorite
    1. COYou need to do two things (1) enable TCP/IP on SQL Server instance and configure it (including ports), and (2) open the right ports on the firewall. Can you describe how you attempted to do both? Did you see any failures or does it just not work? Can you also say if you can see your configuration changes when you open up the SQL Server Configuration Manager and Windows Firewall settings UI?
      singulars
      1. POEnable tcp\ip remote connections to sql server express already installed database with code or script(query)
      1. USKrzysztof Kozielczyk
    2. COYeah I managed to enable TCP/IP when Installing the database. Thanks to your answer I managed to install it by doing: http://stackoverflow.com/a/9154540/637142 . ... take a look at my edit I will explain in more detail the things that I am missing on doing...
      singulars
      1. POEnable tcp\ip remote connections to sql server express already installed database with code or script(query)
      1. USTono Nam
    3. COSomething bad has happened, as your EDIT 3 shows up as empty.
      singulars
      1. POEnable tcp\ip remote connections to sql server express already installed database with code or script(query)
      1. USKrzysztof Kozielczyk
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload