How to create Database & DSN for using MS SQL Server Database

Database is required in most of all the applications to store the applications data. There are most of the applications require either Microsoft SQL Server or Oracle Database. In vSphere Infrastructure also you will require database which you use external or embedded one. If you are going for an Embedded database then you don’t need to do anything to setup or configure the database but in case of external database you will have to setup & configure the database to use it in vSphere.

This post is recommended for LAB purpose only if you are in production environment then it is always better to consult with Database team so you will not face any issues. As database server’s are very critical to business.

In this post first we will see on How to create Database then How to create a User & assign user that Database. Then we will be looking at How to create DSN to setup the connectivity with Database.

Creating a Database in MS SQL Server:

Step 1:
Open the SQL Server Management Studio.
Login using the appropriate credential.
Right click Database > New Database.

Step 2:
Provide the Database name & click OK to create database.

That’s it we have successfully created a Database. Now it’s time to create a user & assign the database to the user.

It is always better practice to create a user for specific database.

Creating a User & Assign database permissions:

Step 1:
Right click Security > New > Login.

Step 2:
First provide the Login name which will be the username while connecting to the database from outside.
Choose SQL Server authentication & set the password for the user.
Change the Default database to the database you had created earlier.

Step 3:
Now click on User Mapping from left side pane.
Choose the Database which you have created.
Set the role for the database such as db_owner.
Click OK to save & close.

At this point we have successfully created a new database & created a user for the database which we will be using while configuring the DSN.

Setting Up & Configuring DSN for database:

Step 1:
Open the Server Manager in Windows Server.
Click on Tools.
Choose the ODBC Data Sources. (Select based on requirement.)

Step 2:
DSN wizard will open here.
Choose the Tab System DSN.
Click Add button.

Step 3:
You will see the SQL Server Native Client.
For this you will need to download & install SQL Server Native Client then only it will be listed here. You can download it from Microsoft web site.
Select SQL Server Native Client & click Finish to add it.

Step 4:
Provide the DSN Name & Description which will help you to identify when you will be deploying vCenter Server or any other object.
Provide the SQL Server name to which this DSN will be connect & use.
Click Next.

Step 5:
Provide the Authentication which you want to use.
In our case we have setup the user for our database so we are going with SQL Server authentication.
Provide the Login ID & Password of the database which you will be using.
Click Next.

Step 6:
Change the default database to your created database which you will be using.
Click Next.

Step 7:
These are the advanced settings make changes only if needed.
Click Finish.

Step 8:
Once you click Finish you will get this windows.
Click Test Data Source to test the DSN connectivity with Database.

That’s it Now you can see that your DSN connectivity with database is successful. If you get any error in connecting then check the permissions for the user.

Click OK to save & close.  Now you can see that your DSN is listed under the System Data sources.

Click OK to save & close.

Now you will see an database DSN you have created by choosing an external database configuration in the installation.

DSN will be created on the server where you want to use external database & connect to the database server.

Using the above steps you can easily setup & configure database for your vSphere object.

Note: This was post was performed in SQL Server 2014 & Windows Server 2012 R2. If you are planning in your production environment please consult with database team & first check for the database compatibility.

That’s it for Today Friends. I Hope you liked reading this post & If you find anything more to be added or removed feel free to write it in our comments. If you find it useful You are Feel free to share this on social media to help others & spread knowledge.
If you have any query on any thing you are free to write it in our comments section & we will make sure to provide you the better solution as soon as possible.

Join Our LinkedIn Group to get Fast updates about our posts : Mastering VMware.
Checkout our Facebook Group for discussions & more.
You can also Like & Share our Facebook Page for Latest Updates.