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.
Or maybe I will have to create a "SQL Server 2008 Server Project" in visual studio.
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. Take a look at the question in here
I found these links that claim on doing something similar and I still cannot make it work.
1) http://support.microsoft.com/kb/839980
2) http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/c7d3c3af-2b1e-4273-afe9-0669dcb7bd02/
3) http://www.sql-questions.com/microsoft/SQL-Server/34211977/can-not-connect-to-sql-2008-express-on-same-lan.aspx
4) http://datazulu.com/blog/post/Enable_sql_server_tcp_via_script.aspx
As Krzysztof stated in his response I need (plus other things that I know that are required)
1 - enable TCP/IP
I have managed to do this when installing a new instance of SQLExpress passing the parameter /TCPENABLED=1
. When I install sql express like in this example. that instance of sql express will have TCP/IP enabled
2 - Open the right ports in the firewall
(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:
netsh firewall set portopening protocol = TCP port = 1433 name = SQLPort mode = ENABLE scope = SUBNET profile = CURRENT
3 - Modify TCP/IP properties enable a IP address
I have not been able to figure out how to enable a IP, change a port etc.. I think this will be the step more complicated to solve
4 - Enable mixed mode authentication in sql server
I have managed to do this when installing SQL Express passing the parameter /SECURITYMODE=SQL
refer to step 1's link.
SQL Server express requires this authentication type to accept remote connections.
5 - Change user (sa) default passowrd
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:
ALTER LOGIN [sa] WITH PASSWORD='*****newPassword****'
6 - finally
will be able to connecto if all the last steps are satisied as:
SQLCMD -U sa -P newPassword -S 192.168.0.120\SQLEXPRESS,1433
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.
Right-click on your server name and click 'Properties'. Go to the Security page for Server Authentication, and select 'SQL Server and Windows Authentication' mode. Then, go to the Connections page and ensure that "Allow remote connections to this server" is checked, and click OK.
Using SQL Server Management StudioIn Object Explorer, right-click a server and select Properties. Select the Connections node. Under Remote server connections, select or clear the Allow remote connections to this server check box.
On the Start menu, click All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager. Click SQL Server 2012 Services. Expand the SQL Server 2012 Network Configuration node, and then select Protocols for MSSQLServer (SQL Instance Name) . Right-click TCP/IP, and then click Enable.
I tested below code with SQL Server 2008 R2 Express and I believe we should have solution for all 6 steps you outlined. Let's take on them one-by-one:
We can enable TCP/IP protocol with WMI:
set wmiComputer = GetObject( _ "winmgmts:" _ & "\\.\root\Microsoft\SqlServer\ComputerManagement10") set tcpProtocols = wmiComputer.ExecQuery( _ "select * from ServerNetworkProtocol " _ & "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 <> 0 then Wscript.Echo "Failed!" end if next end if
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 :-)
We can use WMI again. Since we are using static port 3456, we just need to update two properties in IPAll section: disable dynamic ports and set the listening port to 3456
:
set wmiComputer = GetObject( _ "winmgmts:" _ & "\\.\root\Microsoft\SqlServer\ComputerManagement10") set tcpProperties = wmiComputer.ExecQuery( _ "select * from ServerNetworkProtocolProperty " _ & "where InstanceName='SQLEXPRESS' and " _ & "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 "" & tcpProperty.PropertyName & " set." else Wscript.Echo "" & tcpProperty.PropertyName & " failed!" end if next
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.
Just a reminder that when working with WMI, WBEMTest.exe is your best friend!
I wish we could use WMI again, but unfortunately this setting is not exposed through WMI. There are two other options:
Use LoginMode
property of Microsoft.SqlServer.Management.Smo.Server
class, as described here.
Use LoginMode value in SQL Server registry, as described in this post. Note that by default the SQL Server Express instance is named SQLEXPRESS
, so for my SQL Server 2008 R2 Express instance the right registry key was HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQLServer
.
You got this one covered.
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.
SQLCMD -U sa -P newPassword -S 192.168.0.120,3456
Please let me know if this works for you (fingers crossed!).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With