Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I tested below code with <a href="http://www.microsoft.com/en-gb/download/details.aspx?id=30438" rel="nofollow noreferrer">SQL Server 2008 R2 Express</a> and I believe we should have solution for all 6 steps you outlined. Let's take on them one-by-one:</p> <h2>1 - Enable TCP/IP</h2> <p>We can enable TCP/IP protocol with <a href="http://msdn.microsoft.com/en-us/library/aa394582%28v=vs.85%29.aspx" rel="nofollow noreferrer">WMI</a>:</p> <pre><code>set wmiComputer = GetObject( _ "winmgmts:" _ &amp; "\\.\root\Microsoft\SqlServer\ComputerManagement10") set tcpProtocols = wmiComputer.ExecQuery( _ "select * from ServerNetworkProtocol " _ &amp; "where InstanceName = 'SQLEXPRESS' and ProtocolName = 'Tcp'") if tcpProtocols.Count = 1 then ' set tcpProtocol = tcpProtocols(0) ' I wish this worked, but unfortunately ' there's no int-indexed Item property in this type ' Doing this instead for each tcpProtocol in tcpProtocols dim setEnableResult setEnableResult = tcpProtocol.SetEnable() if setEnableResult &lt;&gt; 0 then Wscript.Echo "Failed!" end if next end if </code></pre> <h2>2 - Open the right ports in the firewall</h2> <p>I believe your solution will work, just make sure you specify the right port. I suggest we pick a different port than 1433 and make it a static port SQL Server Express will be listening on. I will be using 3456 in this post, but please pick a different number in the real implementation (I feel that we will see a lot of applications using 3456 soon :-)</p> <h2>3 - Modify TCP/IP properties enable a IP address</h2> <p>We can use WMI again. Since we are using static port 3456, we just need to update two properties in <strong>IPAll</strong> section: disable dynamic ports and set the listening port to <code>3456</code>:</p> <pre><code>set wmiComputer = GetObject( _ "winmgmts:" _ &amp; "\\.\root\Microsoft\SqlServer\ComputerManagement10") set tcpProperties = wmiComputer.ExecQuery( _ "select * from ServerNetworkProtocolProperty " _ &amp; "where InstanceName='SQLEXPRESS' and " _ &amp; "ProtocolName='Tcp' and IPAddressName='IPAll'") for each tcpProperty in tcpProperties dim setValueResult, requestedValue if tcpProperty.PropertyName = "TcpPort" then requestedValue = "3456" elseif tcpProperty.PropertyName ="TcpDynamicPorts" then requestedValue = "" end if setValueResult = tcpProperty.SetStringValue(requestedValue) if setValueResult = 0 then Wscript.Echo "" &amp; tcpProperty.PropertyName &amp; " set." else Wscript.Echo "" &amp; tcpProperty.PropertyName &amp; " failed!" end if next </code></pre> <p>Note that I didn't have to enable any of the individual addresses to make it work, but if it is required in your case, you should be able to extend this script easily to do so.</p> <p>Just a reminder that when working with WMI, <a href="http://technet.microsoft.com/en-us/library/cc180684.aspx" rel="nofollow noreferrer">WBEMTest.exe</a> is your best friend!</p> <h2>4 - Enable mixed mode authentication in sql server</h2> <p>I wish we could use WMI again, but unfortunately this setting is not exposed through WMI. There are two other options:</p> <ol> <li><p>Use <code>LoginMode</code> property of <code>Microsoft.SqlServer.Management.Smo.Server</code> class, as described <a href="http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.loginmode(v=sql.100).aspx" rel="nofollow noreferrer">here</a>.</p></li> <li><p>Use LoginMode value in SQL Server registry, as described in <a href="https://stackoverflow.com/questions/2070719/how-to-change-from-sql-server-windows-mode-to-mixed-mode-using-sql-sql-server">this post</a>. Note that by default the SQL Server Express instance is named <code>SQLEXPRESS</code>, so for my SQL Server 2008 R2 Express instance the right registry key was <code>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQLServer</code>.</p></li> </ol> <h2>5 - Change user (sa) default password</h2> <p>You got this one covered.</p> <h2>6 - Finally (connect to the instance)</h2> <p>Since we are using a static port assigned to our SQL Server Express instance, there's no need to use instance name in the server address anymore.</p> <pre><code>SQLCMD -U sa -P newPassword -S 192.168.0.120,3456 </code></pre> <p>Please let me know if this works for you (fingers crossed!).</p>
    singulars
    1. This table or related slice is empty.
    1. POEnable tcp\ip remote connections to sql server express already installed database with code or script(query)
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USKrzysztof Kozielczyk
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POEnable tcp\ip remote connections to sql server express already installed database with code or script(query)
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTBountyClose
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWow sql server makes it so difficult to deploy an application with it's database. It is as if they don't want people deploying applications with their database. I wonder if people ever chose this option. Or perhaps they want to make it tough for the people installing it...
      singulars
      1. PO
      1. USTono Nam
    2. CODisclaimer: I work on SQL Server Express. Still, I needed to do a lot of digging to get to the answer. SQL Server is first and foremost a database server, and that means it is sometimes not optimized for other scenarios. The new LocalDB version of SQL Server Express is a better fit for the embedded DB scenario, but it won't work for you as it doesn't accept remote connections. Still, check it out in case the next time you need to embed a database and don't need remote access: http://blogs.msdn.com/b/sqlexpress/archive/2011/11/28/simple-installer-for-localdb-has-shipped.aspx
      singulars
      1. PO
      1. USKrzysztof Kozielczyk
    3. COI will thanks a lot
      singulars
      1. PO
      1. USTono Nam
 

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