When Windows Firewall Is ON, SQL Server Does Not Get Connected Thru TCP
Jul 27, 2006
I am using SQL Server 2005 Express + SP1 on a Windows Small Business Server(SBS) box. The SBS is connected to a client thru LAN.
Following are what I gave as IP address and DNS on the server:
IP: 192.168.16.2, subnet mask : 255.255.255.0, Preferred DNS server: 192.168.16.2, Default gateway and Alternate DNS Server blank
On the client, I have,
IP: 192.168.16.4, subnet mask : 255.255.255.0, Preferred DNS server, Default gateway and Alternate DNS Server blank
I can ping and connect to either of the machines.
If I do a sqlcmd -S "tcp:servernameINSTANCE,port", I get the following error message:
HResult 0x80090304, Level 16, State 1
SQL Network Interfaces: The Local Security Authority cannot be contacted
Sqlcmd: Error: Microsoft SQL Native Client : Cannot generate SSPI context
If I do a sqlcmd -S "tcp:192.168.16.2INSTANCE,port", it connects to SQL Server.
I have the Windows Firewall ON. If I Off the firewall, I do not have any problem at all.
I included File and Printer Sharing, sqlsvr.exe in the Exception list of the Windows Firewall.
Any help to solve the issue is appreciated.
Thanks.
View 7 Replies
ADVERTISEMENT
Apr 18, 2008
Hello,
I have purchased 4 new boxes for SQL2005 and my var database product all running on the new Windows 2008 server. A lot of silly mind bender issues but I am up and live now. However, what should the firewall settings be on my SQL database box? I have ultimately turned off Windows Firewall so that I could connect and continue forward.
Thanks!
Chris
View 9 Replies
View Related
Jun 7, 2006
I have changed SQL Server port to 2433, and add it to exception in Windows Firewall, add executive files as in this KB http://msdn2.microsoft.com/en-us/library/ms175043.aspx
Want to change port of SQL Server Browser as well, but dont know how to :(
anyway, after enable Firewall, SQL server is stop working. How to get it working with Firewall? Also, if some one lets me know how to change port of SQL Server Browser too, it would be great
thanks
View 3 Replies
View Related
Nov 6, 2015
We have a hosted server with a website and sql server 2008 on.
The windows firewall is turned on for all networks / profiles and says its blocking incoming traffic.
I see a lot of login attempts in the windows logs where hackers are trying to connect to it. I can also access it via SSMS from multiple external ips.
Everywhere I've googled says that incoming traffic should be blocked by default by the firewall.
There are no obvious rules that allow SSMS access.
If I do a blocking rule on inbound rules for a single ip that does work, blocking it from that ip, but any other ip's can still connect.
In the firewall log file I can see the allowed connections on port 1433 etc. other than the dropped one from the test blocking rule.
View 5 Replies
View Related
Apr 22, 2008
I am trying to install SQl express as part of a custom app.I have been able to use the template.ini to pass in all the parameters and install. installs fine,however the installation isnt browseable by remote machines due to the sqlserver.exe and sqlbrowser.exe not being in the windows firewall list. How can i add then to this list automatically at install time? or is there another way around this?
View 1 Replies
View Related
Sep 20, 2006
Hi all,
I have a Windows Server 2003 with SQL Server 2005.
I´ve configured the following itens to allow remote connecitions:
[code]
WORKAROUND
Warning If you use Registry Editor incorrectly, you may cause
serious problems that may require
you to reinstall your operating system. Microsoft cannot
guarantee that you can solve problems that result from using
Registry Editor incorrectly. Use Registry Editor at your own
risk.To work around this problem, follow these steps on the
computer that Windows XP SP2 is installed on:
1. Make sure that the Log On As account for the MSDTC service
is the Network Service account. To do this, follow these steps:
a. Click Start, and then click Run.
b. In the Run dialog box, type Services.msc, and then click OK.
c. In the Services window, locate the Distributed Transaction
Coordinator service under Name in the right pane.
d. Under the Log On As column, see whether the Log On As
account is Network Service or Local System.
If the Log On As account is Network Service, go to step 2.
If the Log On As account is Local System, continue with these steps.
e. Click Start, and then click Run.
f. In the Run dialog box, type cmd, and then click OK.
g. At the command prompt, type Net stop msdtc to stop the MSDTC service.
h. At the command prompt, type Msdtc €“uninstall to remove MSDTC.
i. At the command prompt, type regedit to open Registry Editor.
j. In Registry Editor, locate, and then delete the following subkey:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTC
k. Close Registry Editor.
l. At the command prompt, type Msdtc €“install to install MSDTC.
m. At the command prompt, type Net start msdtc to start
the MSDTC service.
Note The Log On As account for the MSDTC service is set
to the Network Service account.
2. To allow the network transaction, you must enable
MSDTC. To do this, follow these steps:
a. Click Start, and then click Run.
b. In the Run dialog box, type dcomcnfg.exe, and then click OK.
c. In the Component Services window, expand Component
Services, expand Computers, and then expand My Computer.
d. Right-click My Computer, and then click Properties.
e. In the My Computer Properties dialog box, click
Security Configuration on the MSDTC tab.
f. In the Security Configuration dialog box, click to
select the Network DTC Access check box.
g. To allow the distributed transaction to run on this
computer from a remote computer, click to select the Allow Inbound check box.
h. To allow the distributed transaction to run on a
remote computer from this computer, click to select the Allow
Outbound check box.
i. Under the Transaction Manager Communication
group, click to select the No Authentication Required option.
j. In the Security Configuration dialog box, click OK.
k. In the My Computer Properties dialog box, click OK.
3. Configure Windows Firewall to include the MSDTC
program and to include port 135 as an exception. To do this,
follow these steps:
a. Click Start, and then click Run.
b. In the Run dialog box, type Firewall.cpl, and then click OK.
c. In Control Panel, double-click Windows Firewall.
d. In the Windows Firewall dialog box, click Add
Program on the Exceptions tab.
e. In the Add a Program dialog box, click Browse,
and then locate the Msdtc.exe file. By default, the
file is stored in the Installation drive:WindowsSystem32 folder.
f. In the Add a Program dialog box, click OK.
g. In the Windows Firewall dialog box, click to select
the msdtc option in the Programs and Services list.
h. Click Add Port on the Exceptions tab.
i. In the Add a Port dialog box, type 135 in the Port
number text box, and then click to select the TCP option.
j. In the Add a Port dialog box, type a name for the
exception in the Name text box, and then click OK.
k. In the Windows Firewall dialog box, select the name
that you used for the exception in step j in the
Programs and Services list, and then click OK.
[/code]
But, when the Windows firewal on the server is "On", remote connections
are not allowed, despite I´ve configured the Exceptions on the firewall.
Is there another task to solve this problem?
thanx!!!!
View 1 Replies
View Related
Oct 19, 2007
im using windows xp pc, im new to SQL could any one provide details about how can i get connected to SQL management express using windows authentication,Im getting error CANNOT ABLE TO CONNECT TO SERVER...
View 5 Replies
View Related
Oct 19, 2007
im using windows xp pc, im new to SQL could any one provide details about how can i get connected to SQL management express using windows authentication,Im getting error CANNOT ABLE TO CONNECT TO SERVER...
View 1 Replies
View Related
Jan 4, 2007
Leon writes "I am trying to connect to a W2K3 Server's 2005 Reporting Server through my laptop's (WXP Pro SP2) SQL Server Management Studio.
I turned Port 1433 on in the server's Windows Firewall and was able to connect to the server's database engine from my laptop, but I have to turn Windows Firewall on the server off completely to be able to connect to the Reporting Server.
I have tried all the ports listed for SQL Server in the Microsoft documentation (TCP and UDP), but they seem to have no effect.
Can anyone tell me which port or ports I need to turn on in the server's firewall so that I can access Reporting Services through my laptop's SSMS?
Thanks in advance.
LGR"
View 1 Replies
View Related
Aug 14, 2007
We install SQL Express 2005 with a custom named instance. Since a named instance uses dynamic ports, how can I add this named instance to the Windows Firewall exception list? Previously with MSDE 2000 we installed as default, then I added port 1433 to the Firewall exception list.
Is there a way to install SQL Express to a static port (programmatically)? Or, is there a better method, like adding the SQL custom named instance service to the firewall exception list?
View 1 Replies
View Related
Jul 20, 2005
My ISP recently had me reset my TCP/IP stack. After that, Norton PersonalFirewall prompted me twice that SQL Server was trying to access theInternet. Both times I responded to allow it to and to always use thataction. Now I am not able to use SQL Server with NPF enabled. If I disableNPF, SQL Server works fine.I am using the desktop edition of SQL Server 7, on a standalone PC, notconnected to a server. I have been using SQL Server and NPF together forover a year. Now, since my TCP/IP stack was reset, NPF interferes with SQLServer.Anyone have any experience with this?Thanks,Neil
View 3 Replies
View Related
Jan 6, 2000
I am trying to connect to SQL Server 7.0 from a web server. The web server is outside our firewall, SQL Server is inside the firewall. I want to use a multiprotocol connection so I can encrypt it. We have port 1433 open on our firewall for TCP/IP from SQL Server. When I connect with just TCP/IP it works. When I switch to multiprotocol it fails with error code 1701. I can connect to the SQL Server box from inside the firewall using multiprotocol.
My theory is that when you use multiprotocol you are using another port than 1433. Is this the answer?
View 1 Replies
View Related
Aug 3, 2004
I have a simple asp page that queries a database inside our firewall. It works fine from the inside, and only uses port 1433. But if i put it out on our web server it won't connect and tries to use ports 139 and 445. Has anybody ran across this before?
Thanks,
John
View 10 Replies
View Related
Jul 20, 2005
Hello,We use Informix and MySQL on linux/unix to drive our web application.SQL*Server is used only for backend enterprise applications within thefirewall. I am trying to get the management to use SQL*Server outside thefirewall. They tell me there are security issues with Microsoft products,including SQL*Server, that make it vulnerable to attacks outside thefirewall. Can someone please point me to white papers/documentation thatsuggests how SQL*Server can be used securely outside the firewall? I thinkif I put SQL*server on it's own box and open it up only to the applicationson our web servers, we should be secure. However, I need hard evidence.Thanks.- Rajesh
View 2 Replies
View Related
Mar 23, 1999
When I try to connect to my SQL server I get the following error.
Microsoft SQL Enterprise Manager
A connection could not be established to MLM1-[SQL Server] Cant allocate space for object 'Syslogs' in database 'tempdb' because the 'logsegment' segment is full. If you ran space in syslogs dump the transaction log. Otherwise use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
Ok, I am not up on SQL so I'm not sure how to dump my tempdb database. I have a coworker that has only done this sort of thing in the Enterprise Manager and we can't get there. So how do I do this and make sure that I don't blow away any data? Also if I do dump my tempdb will this cure my problems and how do I avoid having this happen again?
Any help would be greatly appreciated,
Jeb
View 1 Replies
View Related
Apr 25, 2008
Hi,
Is there any concerns or problems with forward a port to SQL server
from our internet firewall so that you can access the databases over the internet? Is it a standard practice to do this in order to remotely access the SQL server. Or is there a better way to do this? Also, if it is ok to do this are any things you need to do to enhance your security from vulnerabilities being open to the internet.
Any suggestions or comments would be appreciated on this subject.
Thanks
View 2 Replies
View Related
Jun 6, 2007
I cannot seem to find the definitive how-to guide for this. Can anyone point me in the right direction?
I have SQL Server 2005 (x64) already running on Windows 2003 R2 (x64). I'm having difficulty trying to expose it the Internet.
Using the Surface Area Configuration tool I can see that remote connections using TCP/IP is enabled. I also made the machine administrator a member of the SysAdmin role from here too.
In Windows Firewall "SQL Server" and "Web" are already checked as exceptions.
I know that SQL Server uses port 1433 and I also read somewhere that clients connecting to SQL Server communicate on a random port between 1024 and 5000. So, on my router (Netgear WNR854T) I'm forwarding ports 1024 through 5000 to the machine where SQL Server lives.
In my connection string I'm using the IP address of the SQL Server machine with ":1433" appended to the address. Yet I cannot connect to SQL Server. Am I missing anything?
View 9 Replies
View Related
Jan 7, 2006
using vs2005 to build web pages of asp.net 2.0, The database is SQL Server 2000,.But I always fail to connect.
using the following configuration in web.config:
<appSettings> <add key="DSN_student" value="server=(local);uid=admin;pwd=123456;database=network_course"/> </appSettings>
I also failed to connect using the following configuration in web.config:
<add name="network_courseConnectionString1" connectionString="Data Source=(local);Initial Catalog=network_course;User ID=admin;Password=123456;" providerName="System.Data.SqlClient" />
I am a beginner from China and eager to get answers! Thank you!
View 2 Replies
View Related
Apr 10, 2001
I have got a MTS Server and SQL server 7 in different boxes. However, MTS only communicates to SQL server when there is no firewall while it doesn't work when there is a firewall between them. Any idea I can solve this problem?
Many thanks.
William
View 2 Replies
View Related
Oct 16, 2007
Can anyone tell me if there's a recommended way to link from a SQL 2000 SP4 DB through a firewall to SQL 2005? I have tried with SQLOLEDB but this uses NETBios which is not secure. Or is there a way to tell it not to use NETBios?
SET @provdetail = 'DRIVER={SQL SERVER};SERVER=<IP address>;Trusted_connection=Yes'
EXEC Master..sp_addlinkedserver
@server = ,'<IP address>',
@srvproduct = '',
@provider = 'SQLOLEDB',
@provstr = @provdetail ,
@catalog = 'TestDB',
@datasrc ='<IP address>DB1'
View 1 Replies
View Related
Jan 18, 2008
I have uploaded my site on localhost and sharing on intranethow can i answer these question? Q1) How many users have loggedin? and using my database?Q2) Which table has lots of load?Q3) how can i immediately close particularly connection?
View 4 Replies
View Related
Dec 8, 2005
Hi !
I need to maintain a record such as how many time any user (e.g, sa) connects to the sql server. Means whenever any person is connecting to the database through application or directly, then i need to know that through which sql user(e.g sa), any body connected.
Regards,
Shabber Abbas Rizvi.
View 1 Replies
View Related
Jul 20, 2005
Is there a utility that will allow me to see what users on my network areconnected/active on SQL server?Ryan
View 4 Replies
View Related
Jun 19, 2015
I have trying to access a 2nd SQL Server instances running on my WHS 2011 server from my Windows 8.1 Pro client.
The first SQL Server instance I installed is SQL Server 2014 Developer. This has been in place for many months and access from my client pc is not a problem. The Developer instance was installed using standard settings so uses TCP port 1433 and I have verified this by checking the firewall rules. This instance name is <SERVERNAME>
As I want to host a personal website on my server I installed SQL Server 2014 Express as a 2nd instance on my WHS 2011 box for website database purposes. After this installation both instances are accessible via Management Console on the WHS 2011 box.Â
The second instance is named as <SERVERNAME>SQLEXPRESS.
However, I am unable to access the SQLEXPRESS instance using Management Console on my client pc. I know it is a firewall issue as when I turn off the firewall on the WHS 2011 box I can connect successfully. The errors reported have resulted in my trying many things to resolve the problem but none have worked, e.g.
Use the default SQLEXPRESS instance to listen on all TCP ports (default installation option). I checked the necessary firewall rules were set up correctly and they were, i.e. UDP ANY and TCP ANY for the SQLEXPRESS sqlservr.exe file.
Change the default SQLEXPRESS instance to list on a specific TCP port, e.g. 19344
Create an ALIAS for the SQLEXPRESS installation
The instance is definitely set up for Remote Access and is not Hidden.
Are there any other firewall rules I need to apply?
View 2 Replies
View Related
Aug 25, 2006
Hello,
My question is about allowing and securing connections to SQL Server 2000 over the internet. The company that I work for has an application server that several of our clients connect to via the internet using secure .NET remoting. Basically, the clients have a desktop application that they run that creates a remoting connection to our server software and we handle the server/database part. Anyway, one of our clients now wants to use Crystal Reports to run ad hoc queries on their data that is hosted on our SQL 2000 database server behind our firewall. Obviously, opening up a port in our firewall and allowing someone to run ad hoc queries on the database makes us all more than a little nervous about security.
Has anyone else here had to deal with this sort of situation before? We'd like to set up a secure, encrypted connection for this one client, but still keep it locked down for everyone else. Is it as simple as enabling encryption and generating SSL certificates for the client machine and our server? I've only been able to find a few resources that help with bits and pieces of the problem, never anything tackling the issue as a whole. If anyone has any thoughts, experiences, links, etc. to share it would be greatly appreciated. We are a small company and no one here has experience with this sort of thing.
Cheers!
Justin
View 4 Replies
View Related
Sep 16, 2015
I have a query that finds all SPID's connected to a particular database:
select d.name, p.*
from sys.databases d join sys.sysprocesses p
on d.database_id = p.dbid
where d.name = 'my_db'
But now we have a new rule that we should not use outdated compatibility views, and one of them is sys.sysprocesses. I checked sys.dm_exec_connections/session/requests but failed to replace my existing code. The first two don't have dbid, the last one, requests, has it, but it selects only currently executing statements.
View 0 Replies
View Related
Sep 5, 2005
I have read that if I change "computer name" and then try to startup sql server it will recognize the changes automatically BUT you need to execute sp_dropserver and sp_addserver procedures too in order to set all thing right.But yesterday I tried it on a Personal edition on xp and after reboot sql server came up without any problem and I could connec it without executing sp_dropserver and sp_addserver! Was it a specific situation or no need to those SPs at all?
View 10 Replies
View Related
Mar 6, 2006
Hi,
I'm experiencing problems connecting remotely (through the Management Studio) to a named instance of SQL Server Express 2005.
After investigation I determined it was a firewall issue - turn off windows firewall and I can connect fine. I initially added ports 1433 and 1434 to windows firewall - still no joy. Then I added the binaries explicitly (sqlservr.exe and sqlbrowsr.exe - or whatever they are) - still no joy. So, I looked into the firewall log to see what was being dropped. I found that my IP was trying to connect via port 1047 (TCP)... I've searched for anything about this on google and cannot find any indication that the management studio should be using this port to connect.
If I add this port, the connection works fine. Has anyone else experienced this ? As there seems to be no way of modifying the management studio to connect via a specific port, I'm a bit concerned that something is just not right.
I would appreciate any advice you can give.
Thanks.
View 1 Replies
View Related
Jun 8, 2007
Dim strCON As String = _
"workstation id=DJI-MAIN;packet size=4096;user id=sa;integrated security=SSPI;data source=DJI-MAIN;persist security info=False;initial catalog=RacingSystem"
Dim cnn1 As New SqlConnection(strCON)
cnn1.Open()
This is the code I have been using to connect to my default instance for 18 months. A recent upgrade to Windows Live onecare now prevents me connecting to the server from the same machine. The following error message is generated:-
Unhandled Exception: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at TrainerReport001.Form1.Form1_Load(Object sender, EventArgs e) in F:#ProjectsStatsOwnerReport001Form1.vb:line 84
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)The program '[3336] TrainerReport001.exe' has exited with code 0 (0x0).
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at TrainerReport001.Form1.Main() in F:#ProjectsStatsOwnerReport001Form1.vb:line 2
If I turn off the onecare firewall I can connect as normal, but this is not a desired option. I have tried enabling port 1433 on the firewall, this does not work either. Any help greatly appreciated, onecare support seems reluctant to help.
TIA
View 4 Replies
View Related
Feb 25, 2015
Recently I needed to find all processes connected to a particular database, let's call it Test_db. I have a simple query to find all connections to my database:
select *
from sys.databases d join sys.sysprocesses p
on d.database_id = p.dbid
where d.name = 'test_db'
But there was a process that was connected to another database like USE another_db_name; but was actually selecting from tables in test_db. Is it possible to catch such connections?
View 7 Replies
View Related
Jul 20, 2005
Hi,i have a problem about the CLIENT-SERVER architecture procedure.Well , i have an application in VB with ADO connection to a table in adatabase on a SQLSERVER 7.0 .Is possible to do that when a client updates a data in a field of my table ,the SERVER communicates to all clients connected to my table that this dataare updated , without the client do anything , for example without aclient-timer to control the data in the server ???thanks
View 2 Replies
View Related
Mar 18, 2008
We have an information retrieval application in which there is a single connection to a database followed by multiple table open, read, and close commands. Response time is consistantly less than 1 second on a LAN.
When Internet connected (not VPN), the first table read is typically fast, but the response time becomes slower and slower after multiple table open, read, and close commands. There seems to be a considerable amount of handshaking based on monitoring of the router's status lights.
View 7 Replies
View Related
Jun 2, 2004
Hello all,
When I close a web form that has a connection to my SQL Server, I am not seeing the memory process close in task manager (of the SQL Server). I am using the "open late close early" theory of database connections. I am using the "close" method for my database connections. Is there any automated utility that will shut down these processes? I thought when the user was disconnected from the database, the memory process would automatically shut down.
Any suggestions, thoughts, or ideas?
TYIA,
lonelobo
View 1 Replies
View Related
