Tag: SQL Server

How to open up the Windows firewall for SQL Server 2008 on Windows Server 2008 R2

insomniacgeek2 Comments

After you have installed SQL Server, you might have noticed that the firewall will block access to it.

In order to remedy this, you will have to open 2 ports in the firewall.

TCP port 1433 for the actual SQL Server engine, and UDP port 1434 for the SQL Server Browser.

This could be done from the GUI, but I have found that it’s more convenient to do it from an elevated command prompt.

netsh firewall set portopening protocol=TCP port=1433 name = SQLPortEngine
mode=ENABLE scope=SUBNET profile=CURRENT
netsh firewall set portopening protocol=UDP port=1434 name = SQLPortBrowser
mode=ENABLE scope=SUBNET profile=CURRENT

Or follow the manual GUI steps in this MSDN article:

How to: Configure a Windows Firewall for Database Engine Access

IIs requirements for installing SQL Server 2005/8 on Windows Vista or Windows 7

insomniacgeek1 Comment

In order to install SQL Server 2005 or SQL Server 2008, some IIs features must be installed on Windows Vista.

First, install IIs 7 using the following steps:

  1. Start
  2. Control Panel
  3. Programs
  4. Programs and Features
  5. Turn Windows features on or off
  6. Enable <Internet Information services>

Select these features:

  • Web management tools:
    — IIS 6 Management Compatibility
    —- IIS 6 WMI Compatibility
    —- IIS Metabase and IIS 6 configuration compatibility
  • World Wide Web Services
    — Application Development Features
    — ASP.NET
    — ISAPI Extensions
    — ISAPI Filters
  • Common Http Features
    — Default Document
    — Directory Browsing
    — HTTP Redirection
    — Static Content
  • Security
    — Windows Authentication

image

Cannot insert the value NULL into column ”, table ”; column does not allow nulls. INSERT fails.

insomniacgeek

I stumbled upon this error all of a sudden when I was working in a project. There was this SQL Server 2000 database that existed, that very few knew nothing about. This error message confused me a lot, because it didn’t show up when I was running some scripts from the Query Analyzer, but is showed up when I ran the exact same scripts from within a SSIS SQL task.

As it turned out, the database had some unknown owner. Here’s how to find out.

 

SELECT Name AS DBName, suser_sname(sid) AS Owner FROM master.dbo.sysdatabases WHERE suser_sname(sid) IS NULL

 

If you get any results, you can set an owner for those databases using:

 

USE <THE DATABASE NAME>EXEC sp_changedbowner 'sa'

 

That should take care of the problem.