Allow Access of SQL Server Database to Other Computer (When Windows Firewall is OFF)

Introduction:

In this topic, I will explain steps to allow access of SQL server Database to other Computer when Windows Firewall is OFF.

Description:

In previous article, I explained about Restrict Database Access of User in SQL Server. (Please read this article to restrict access of user in shared database)

When you work with your team in single project then it becomes necessary to share SQL server database in many computer. By sharing database, all team members can work on same database which will increase productivity.

SQL server provides database sharing functionality in very easy to configure manner. You just need to follow some steps and you can allow access of any SQL server database to other computer.

Let's jump directly on steps to allow access of your SQL Server database :

1. From Start menu, open SQL Server Configuration Manager.

2. Expand SQL Server Network Configuration from left pane and select protocol for which you like to allow access. It will open Protocol name and current Status of protocol in right pane.

3. In Right pane, from listed protocol select TCP/IP protocol and make it Enabled. You can use either of following ways to make protocol Enabled :

    - Right click on TCP/IP protocol name and select Enable option.

    - Double click on TCP/IP protocol name, it will open TCP/IP properties window. From opened window, select Protocol tab and set Enabled property to Yes and click Ok.

4. After completing above steps, you need to restart SQL Server Browser and SQL Server services. To restart services, go to Run and open services.msc. It will open Services window which includes all services currently available in computer.

5. From opened Services window, right click on SQL Server Browser and SQL Server services and select restart option which will restart both services automatically.

After restarting both services, any user can access SQL Server database.
     

Insert data from CSV file into SQL Server table

Introduction:

Here, I will explain how to insert data from CSV file into SQL Server table.

Description:

Recently, I did one task with requirement to insert bulk data from CSV file into SQL Server table. As you know, this is very simple task if we have same number of columns with same sequence in CSV file as table.

But to accomplish this task, I had CSV file containing same number of columns with same sequence as table EXCEPT ID column of table which was Identity Column with auto increment value. I had tried with various scripts but 1 column was missing in CSV file from table so NO scripts were allowing me to insert data into table.

To resolve error, I simply added dummy ID column into CSV file and data inserted properly into table.

Student table in SQL Server :

Original CSV file (Student.CSV) :

Updated CSV file :

You can use following script to process CSV file and insert data into table :

BULK

INSERT Student -- table name

FROM 'E:\Student.csv' -- Path of CSV file

WITH

(

          FIRSTROW = 2, -- This is used to start reading process from Row 2 of CSV file.

          FIELDTERMINATOR = ',',

          ROWTERMINATOR = '\n'

)

Data in Student table after running script :

Restrict Database Access of User in SQL Server

Introduction: 

Here, I will explain steps to allow only single database access to user in SQL server.

Description:

While publishing SQL server database on hosting, I have found that I can do operation (Insert, Update, Delete, Select) in my published databases only. Here, interesting thing was that even I cannot open new Query windows for other User's database. We can also do same thing in SQL Server. To do this, please follow below steps :

Connect in SQL Server.

Go to Object Explorer.

From Object Explorer, open Security tab / Logins tab. Right click on Logins tab and click on New Login... option which will open window as shown below :

In General tab, set Login name, select SQL Server authentication and set password and uncheck Enforce password policy option.

In User Mapping tab, select database (You can select any number of databases) for which you like to restrict access for User from "Users mapped to this login" section and select all roles EXCEPT "db_denydatareader" and "db_denydatawriter" from "Database role membership for : Selected Database name" section (You can select any number of roles from list).

After selecting database and roles, click OK.