Monday, 31 July 2017

Reset Password for Azure SQL Database

Reset Password for Azure SQL Database



It is very common for all of us to forget the passwords/credentials and thankfully there are ways to reset them. However hard we try to remember the passwords we tend to forget them for some reasons over the time. Last week the similar issue happened with me as well. While I was working on an Azure SQL Database problem (which is also referred as Platform as a service PASS or Database as a service DAAS), I realized that my password was not working as the one which I was entering was wrong. Upon retrying once more I checked and observed that I was getting login failed error which points to wrong credentials supplication -


“TITLE: Connect to Server
------------------------------


Cannot connect to sqldbserver11.database.windows.net,1433.


------------------------------
ADDITIONAL INFORMATION:


Login failed for user 'rahul'. (Microsoft SQL Server, Error: 18456)




------------------------------





Reset Passwords for Azure SQL Database through Microsoft Azure portal











The error was login failed and was shown on the connection screen itself. I have already written a detailed article which you can check here-> Create an Azure SQL Database on how to create a SQL Azure database using Azure Portal and connect to it using SSMS (SQL Server Management Studio). Since admin password is required to get connected to SQL database (as in my case I did not create any other login other than admin account), I thought of resetting it.


To reset password for Azure SQL Database please perform the following steps-



  1. Login to portal.azure.com with your credentials
  2. Click on the SQL Databases-


Reset Passwords for Azure SQL Database using the Azure Portal









3) When you will click on SQL Databases you will be shown all the available SQL Databases that are installed on the azure portal. You will need to identify for which Azure SQL database you want to reset password. Please make sure that you select the database for which you have forgotten the password and want to do a password reset. Example here I have selected Test SQL database-and then I clicked on Overview.


4) You will now need to click on the Server name from the overview window. In my example, I will click on the fully qualified server name which is - sqldbserver11.database.windows.net.


5) You will be presented with another window where you can see the “Reset Password” link.


6) Post clicking to “Reset Password” link you will get a window to reset password for admin account-


Reset Passwords for Azure SQL Database















7) Supply the new password and confirm the new password to reset the admin password for Azure SQL Database. Please make sure you enter a password that meets all the security standards Once you have verified both new password and confirm password fields please click on the save option to save the newly set password for the admin account. After resetting the password, you should come back to your SSMS screen and try to connect again using the new password and you should be able to get in.


After resetting the password for Azure SQL database (test Azure SQL Database in my example) by following the above steps I tried to reconnect the database via SSMS (SQL Server Management Studio) and I was able to get connected to it.

Friday, 28 July 2017

Cannot connect to Azure SQL Server due to firewall issues

Cannot connect to Azure SQL Server due to firewall issues




Today, when I tried to connect to an existing SQL Azure database (you can check my previous post here where I have showed how to create an Azure SQL Database) from my local SSMS(SQL Server Management Studio), I started getting the below error-
Cannot connect to Azure SQL Server due to firewall issues

Upon doing some research, I got to know that you need to add firewall rules in order to enable your Azure SQL Database for remote connections.


You need to follow the below steps to do that step by step-

1)      Log in to portal.azure.com.

2)      Once you are logged in to the Portal successfully then at the left hand side, you will see the button for SQL Databases-> click that

setting firewall rules in Azure SQL database

3)      Select the database for which you want to set the firewall rules and click overview-

setting firewall rules in client and portal for Azure SQL database






4)      You will be presented with all the details like the Resource Group, Status, Server name, Location, Subscription name, pricing tier etc of your Azure SQL Database. On the top of the screen, you will see “Set Firewall rules” option. You need to click on that option to set the firewall rules.

5)      Upon clicking “Set firewall rules” option you will be presented with the below screen-

set firewall rules in Azure SQL Database












6)      You need to give a rule name in the “Rule Name” Box and specify start IP and End IP of the client or clients from whom you want the connection to be established. If you want that only one IP should get access to connect to the Azure SQL Databases, then you can just keep the Start IP and End IP same. Otherwise you can specify a range for example- “X.X.X.1 to X.X.X.10”.

7)      Once you have entered the Start IP and End IP, you can click on Save button. Please note that it may take up to 5 minutes for the changes to take effect. You may receive a similar error which is copied below-
“TITLE: Connect to Server
------------------------------
Cannot connect to sqldbserver11.database.windows.net,1433.
------------------------------
ADDITIONAL INFORMATION:
Cannot open server 'sqldbserver11' requested by the login. Client with IP address '59.160.69.66' is not allowed to access the server.  To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range.  It may take up to five minutes for this change to take effect. (Microsoft SQL Server, Error: 40615)
Besides this, you will also need to enable SQL Port in your firewall setting. By default the SQL Server listens to port number 1433 but in some cases due to security reasons you can also see a port other than the default one. Below is the example to add 1433 and 1434 ports in Windows Firewall settings-
Microsoft Windows [Version 10.0.14393]
(c) 2016 Microsoft Corporation. All rights reserved.
C:\WINDOWS\system32>netsh advfirewall firewall add rule name="SQL Server Browser" protocol=UDP dir=in localport=1434 action=allow
Ok.
C:\WINDOWS\system32>netsh advfirewall firewall add rule name="SQL Server Engine" protocol=TCP dir=in localport=1433 action=allow
Ok.
8)      After adding the IP of my client machine in the firewall settings, I tried to connect Azure SQL Database from my local SSMS and I was able to connect to it.



So, We have seen that by adding firewall rules both in the portal and windows (client) we can connect to an Azure SQL database locally as well.

Monday, 24 July 2017

Creating Azure SQL Database from Azure Portal

Creating a new Azure SQL Database from Azure Portal

Azure SQL Database is a database-as-a-service which is highly performance-driven, secure and trustworthy. With Azure SQL Database, you can now create websites or applications without requiring to manage the infrastructure. This enables you to concentrate more on data and application stuff and back-end is managed by Microsoft. Today we will learn how to create Azure SQL Database using Azure Portal.


Please follow below steps-

  1. You need to login to Azure Portal-> portal.azure.com. Please sign up on it if you don’t have an account already.
  2. Once you login to the portal, you need to go to the left pane of the main page and click-> SQL Databases. You will be presented with a window like below-

Create Azure SQL Database using Azure Portal













3) Click Add to add the new Azure SQL Database and you will see the below screen-


configuration settings for Azure SQL Database


4) Fill in all the mandatory details like Database name, Subscription, Resource group, Select source, Server etc. You can use an existing resource group or create a dedicated resource group to be used for the new Azure SQL Database. In Select Source box, you can choose to install a blank database. You also have an option to create a sample database or create a database from a pre-existing backup of any other database.


5) You need to configure Server Setting once you are done with the above specified details. To do that Click on Configure required settings under Server tab. Once you click, you will be presented with another new tab to select an existing server or create a new Server. The screen will look like the one which is copied below-


Configuration and server settings for Azure SQL Database using Azure Portal



6) If we already have a Server we can choose that by selecting that server. In case we want a new Server to be installed altogether for this Azure SQL Database then we will click on the -> Create a new server link and supplying the mandatory information like Server Name, Serveradmin Login, Password and Location. Here in the example I have taken sqltest as the Server Name, Rahul as the Server admin login (please give a strong password for this admin login as you will be later logging in to the SQL Server using this login only). We have selected West Europe as the location. Allow Azure services to access server will be enabled by default and we will leave it as it is.
7) Next, we will have to select the pricing tier for our Azure SQL Database. We can choose among basic, standard, premium and premium rs tiers as per the requirements. Then verify the details of DTU and pricing from the same page and once everything is verified don’t forget to apply the changes.
selecting pricing tiers for Azure SQL Database from Azure Portal











8) Finally, you should select the desired Collation and click on Create.
So, this will create your Azure SQL Database which you can connect from database engine and manage your data. Azure Portal seems very user friendly and direct.