{"id":2015,"date":"2020-12-29T22:40:19","date_gmt":"2020-12-29T22:40:19","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=2015"},"modified":"2020-12-29T22:43:42","modified_gmt":"2020-12-29T22:43:42","slug":"after-azure-sql-login-password-change-error-916","status":"publish","type":"post","link":"https:\/\/www.theSQLReport.com\/?p=2015","title":{"rendered":"After Azure SQL Login Password Change &#8211; Error 916"},"content":{"rendered":"<p>My organization requires periodic password changes on all its accounts.\u00a0 After changing a password for a local login for a Azure SQL Instance, this account was able to login into the Azure SQL Instance, but not connect to its target database.\u00a0 I received the &#8220;Microsoft SQL Server, Error: 916&#8221; when I bought the database in to focus.<\/p>\n<p>&nbsp;<\/p>\n<h3>PROBLEM:<\/h3>\n<p>Using SQL Server Management Studio (SSMS), I changed the login&#8217;s password in the master database using the following SQL:<\/p>\n<pre>-- Using the Master Database \r\nALTER LOGIN AzureSQLuser01 WITH PASSWORD = 'CrAzyL0ng#But#SafePassW0rd' ; \r\nGO<\/pre>\n<p>Again using SSMS to connect with the altered login, I was able to connected Azure SQL Instance.\u00a0 I did see a list of all the databases in the Azure SQL Instance.\u00a0 When I clicked on the database that I had permission, I received the following error:<\/p>\n<p><a href=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2020\/12\/Azure_SQL_Login_Error_916.jpg\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-2016 size-full\" src=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2020\/12\/Azure_SQL_Login_Error_916.jpg\" alt=\"\" width=\"811\" height=\"250\" \/><\/a><\/p>\n<p>Contents of the copied error message:<\/p>\n<h6>TITLE: Connect to Database Engine<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;<br \/>\nCannot connect to my-azure-server7603.database.windows.net.<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;<br \/>\nADDITIONAL INFORMATION:<br \/>\nThe server principal &#8220;AzureSQLuser01&#8221; is not able to access the database &#8220;MyAzureDB&#8221; under the current security context.<br \/>\nCannot open database &#8220;MyAzureDB&#8221; requested by the login. The login failed.<br \/>\nLogin failed for user &#8216;AzureSQLuser01&#8217;. (Microsoft SQL Server, Error: 916)<br \/>\nFor help, click: http:\/\/go.microsoft.com\/fwlink?ProdName=Microsoft%20SQL%20Server&amp;EvtSrc=MSSQLServer&amp;EvtID=916&amp;LinkId=20476<\/h6>\n<h3><\/h3>\n<h3>Resolution:<\/h3>\n<p>Short answer, I had to drop &amp; recreate the user in the problem database.<\/p>\n<ul>\n<li>Need to find the roles that this login has as a user in this database:<\/li>\n<\/ul>\n<pre>--\r\n-- Finding the roles of the current user\r\n--\r\n-- I am not the author of this query - just found it on google\r\n--\r\nSELECT \r\nDP1.name AS DatabaseRoleName,\r\nisnull (DP2.name, 'No members') AS DatabaseUserName\r\nFROM sys.database_role_members AS DRM\r\nRIGHT OUTER JOIN sys.database_principals AS DP1\r\nON DRM.role_principal_id = DP1.principal_id\r\nLEFT OUTER JOIN sys.database_principals AS DP2\r\nON DRM.member_principal_id = DP2.principal_id\r\nWHERE DP1.type = 'R'\r\nORDER BY DP1.name;<\/pre>\n<ul>\n<li>Need to drop &amp; recreate the user in the problem database<\/li>\n<\/ul>\n<pre>--\r\n-- Need to drop &amp; recreate the user in the problem database\r\n--\r\nDROP USER [AzureSQLuser01];\r\nGO\r\n\r\nCREATE USER [AzureSQLuser01] FOR LOGIN [AzureSQLuser01] WITH DEFAULT_SCHEMA=[dbo]\r\nGO\r\n\r\nALTER ROLE db_datawriter ADD MEMBER AzureSQLuser01;\r\nGO\r\n\r\nALTER ROLE db_datareader ADD MEMBER AzureSQLuser01;\r\nGO<\/pre>\n<h3>Sidenote:<\/h3>\n<p>I am not happy with this solution. However, my search of the internet has not found a better answer. This does not happen with all my password changes. Unable to find a root cause on this issue.<\/p>\n<p>If someone has found a better solution then this, or the root cause, please leave it in the comment below, and I will happily update this post.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>My organization requires periodic password changes on all its accounts.\u00a0 After changing a password for a local login for a Azure SQL Instance, this account was able to login into &hellip; <a class=\"readmore\" href=\"https:\/\/www.theSQLReport.com\/?p=2015\">Continue Reading &rarr;<\/a><\/p>\n","protected":false},"author":1,"featured_media":2016,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[193,194],"class_list":["post-2015","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-azure-sql-instance","tag-error-916"],"_links":{"self":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/2015"}],"collection":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2015"}],"version-history":[{"count":3,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/2015\/revisions"}],"predecessor-version":[{"id":2019,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/2015\/revisions\/2019"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/2016"}],"wp:attachment":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2015"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2015"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2015"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}