Access Restrictation with IP address

Question

Post reply

Access Restrictation with IP address

ali.nasir

Old Hand

Points: 343
More actions
March 6, 2003 at 6:52 pm

#357717

Hi..
I would like to create a database for payroll with max security. None with no access granted could be able to access this DB. Is there any way to setup , so only particular IP address can access DB. I use SQL2K run on W2K Advanced Server
ali

ali

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

Use a firewall and drop all packets from any source address except those accepted.

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

Alternately, if you're running all Win2K or higher systems for the client(s) (WinXP/2003), you can use IPSec policies to restrict what IP addresses may access the server. Keep in mind this restricts access to the server, not a particular database.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Andy Warren SSC Guru Points: 119902 More actions · Answer 3

Could also put on a separate sub net, configure the router to only have the one route you need.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

tcartwright SSCertifiable Points: 5573 More actions · Answer 4

Another alternative to the above perfectly fine suggestions is to use a local SW firewall on the OS. I am unsure of whether Chris meant to use a HW or SW firewall. There are quite a few SW firewalls out there. I personally like Sygate. www.sygate.com That would let you block all ip traffic except the one you wanted to allow through by port / subnet / ip. Ipsec will do the same thing, as well as a HW firewall.

Tim C.

//Will write code for food

Tim C //Will code for food

tcartwright SSCertifiable Points: 5573 More actions · Answer 5

AFAIK Andy a drawback to your suggestion is that broadcast traffic (UDP) does not cross subnet boundaries, so the net send alerts would not reach the other subnet.

Tim C.

//Will write code for food

Tim C //Will code for food

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

UDP is on top of IP. It can cross subnetworks (recall SQL Slammer used UDP to port 1434 since it was an attack on the listener service) if it is unicast (directed at a single IP). A broadcast would be where the bits for the host are all 1s. Most routers do split the broadcast domains up, preventing broadcasts.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

tcartwright SSCertifiable Points: 5573 More actions · Answer 7

But the net sends are multicast are they not? So that would prevent them from going across the subnet boundary unless you could configure your router to pass the udp traffic along. I am not completely sure on this one, that's why I said AFAIK.

Tim C.

//Will write code for food

Tim C //Will code for food

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 8

Net send can be TCP directed to port 139. If you specify the hostname, the system wanting to send will try to resolve the name, establish a normal TCP connection, and deliver the net send. Here is an example:



11 13.148907 ISAIAH LOCAL SMB C send message, 

  from ISAIAH to DEVPORT1 ISAIAH DEVPORT1 IP 

Frame: Base frame properties

    Frame: Time of capture = 3/14/2003 2:35:18.186

    Frame: Time delta from previous physical frame: 0 microseconds

    Frame: Frame number: 11

    Frame: Total frame length: 128 bytes

    Frame: Capture frame length: 128 bytes

    Frame: Frame data: Number of data bytes remaining = 128 (0x0080)

ETHERNET: ETYPE = 0x0800 : Protocol = IP:  DOD Internet Protocol

    ETHERNET: Destination address : 00D0592E6A23

        ETHERNET: .......0 = Individual address

        ETHERNET: ......0. = Universally administered address

    ETHERNET: Source address : 00023F7BEBE3

        ETHERNET: .......0 = No routing information present

        ETHERNET: ......0. = Universally administered address

    ETHERNET: Frame Length : 128 (0x0080)

    ETHERNET: Ethernet Type : 0x0800 (IP:  DOD Internet Protocol)

    ETHERNET: Ethernet Data: Number of data bytes remaining = 114 (0x0072)

IP: ID = 0xFD30; Proto = TCP; Len: 114

    IP: Version = 4 (0x4)

    IP: Header Length = 20 (0x14)

    IP: Precedence = Routine

    IP: Type of Service = Normal Service

    IP: Total Length = 114 (0x72)

    IP: Identification = 64816 (0xFD30)

    IP: Flags Summary = 2 (0x2)

        IP: .......0 = Last fragment in datagram

        IP: ......1. = Cannot fragment datagram

    IP: Fragment Offset = 0 (0x0) bytes

    IP: Time to Live = 128 (0x80)

    IP: Protocol = TCP - Transmission Control

    IP: Checksum = 0x7938

    IP: Source Address = 192.168.1.104

    IP: Destination Address = 192.168.1.100

    IP: Data: Number of data bytes remaining = 94 (0x005E)

TCP: .AP..., len:   74, seq: 242309474-242309548, 

  ack:1740731460, win:64508, src: 1875  dst:  139 (NBT Session) 

    TCP: Source Port = 0x0753

    TCP: Destination Port = NETBIOS Session Service

    TCP: Sequence Number = 242309474 (0xE715962)

    TCP: Acknowledgement Number = 1740731460 (0x67C17444)

    TCP: Data Offset = 20 (0x14)

    TCP: Reserved = 0 (0x0000)

    TCP: Flags = 0x18 : .AP...

        TCP: ..0..... = No urgent data

        TCP: ...1.... = Acknowledgement field significant

        TCP: ....1... = Push function

        TCP: .....0.. = No Reset

        TCP: ......0. = No Synchronize

        TCP: .......0 = No Fin

    TCP: Window = 64508 (0xFBFC)

    TCP: Checksum = 0xE4EE

    TCP: Urgent Pointer = 0 (0x0)

    TCP: Data: Number of data bytes remaining = 74 (0x004A)

NBT: SS: Session Message, Len: 70

    NBT: Packet Type = Session Message

    NBT: Packet Flags = 0 (0x0)

        NBT: .......0 = Add 0 to Length

    NBT: Packet Length = 70 (0x46)

    NBT: SS Data: Number of data bytes remaining = 70 (0x0046)

SMB: C send message, from ISAIAH to DEVPORT1

    SMB: SMB Status = Error Success

        SMB: Error class = No Error

        SMB: Error code = No Error

    SMB: Header: PID = 0x0000 TID = 0x0000 MID = 0x0000 UID = 0x0000

        SMB: Tree ID      (TID) = 0 (0x0)

        SMB: Process ID   (PID) = 0 (0x0)

        SMB: User ID      (UID) = 0 (0x0)

        SMB: Multiplex ID (MID) = 0 (0x0)

        SMB: Flags Summary = 0 (0x0)

            SMB: .......0 = Lock & Read and Write & Unlock not supported

            SMB: ......0. = Send No Ack not supported

            SMB: ....0... = Using case sensitive pathnames

            SMB: ...0.... = No canonicalized pathnames

            SMB: ..0..... = No Opportunistic lock

            SMB: .0...... = No Change Notify

            SMB: 0....... = Client command

        SMB: flags2 Summary = 0 (0x0)

            SMB: ...............0 = Understands only DOS 8.3 filenames

            SMB: ..............0. = Does not understand extended attributes

            SMB: ...0............ = No DFS namespace

            SMB: ..0............. = No paging of IO

            SMB: .0.............. = Using SMB status codes

            SMB: 0............... = Using ASCII strings

    SMB: Command = C send message

        SMB: Word count = 0

        SMB: Originator Name = ISAIAH

        SMB: Destination Name = DEVPORT1

        SMB: Data: Number of data bytes remaining = 14 (0x000E)

00000:  00 D0 59 2E 6A 23 00 02 3F 7B EB E3 08 00 45 00   .ÐY.j#..?{ëã..E.

00010:  00 72 FD 30 40 00 80 06 79 38 C0 A8 01 68 C0 A8   .rý0@.€.y8À¨.hÀ¨

00020:  01 64 07 53 00 8B 0E 71 59 62 67 C1 74 44 50 18   .d.S.‹.qYbgÁtDP.

00030:  FB FC E4 EE 00 00 00 00 00 46 FF 53 4D 42 D0 00   ûüäî.....FÿSMBÐ.

00040:  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   ................

00050:  00 00 00 00 00 00 00 00 00 00 00 23 00 04 49 53   ...........#..IS

00060:  41 49 41 48 00 04 44 45 56 50 4F 52 54 31 00 01   AIAH..DEVPORT1..

00070:  0E 00 74 65 73 74 20 74 65 73 74 20 74 65 73 74   ..test test test

The original command was net send devport1 "test test test" from ISAIAH. So if you are using net sends that have to bridge subnets, specify a host.

edited to fit better on screen

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

Edited by - bkelley on 03/14/2003 01:01:33 AM

K. Brian Kelley
@kbriankelley

Antares686 SSC Guru Points: 125444 More actions · Answer 9

The key to multicast and broadcast is if the router is configured to handle the traffic. If it is explicitly not then it will not send the data across.

tcartwright SSCertifiable Points: 5573 More actions · Answer 10

quote:
I'm not worthy!, I'm not worthy!

Seriously, good point Brian, and Antares, I stand rebutted.

Tim C.

//Will write code for food

Tim C //Will code for food

mybid2003 SSCertifiable Points: 6620 More actions · Answer 11

mybid2003

SSCertifiable

Points: 6620

March 20, 2003 at 11:00 pm

#451659

Brian!!! Very nice point...

.