{"id":9409,"date":"2022-04-22T15:30:37","date_gmt":"2022-04-22T10:00:37","guid":{"rendered":"https:\/\/www.skynats.com\/?p=9409"},"modified":"2025-05-23T16:29:17","modified_gmt":"2025-05-23T10:59:17","slug":"change-sql-server-authentication-mode-using-ssms","status":"publish","type":"post","link":"https:\/\/www.skynats.com\/blog\/change-sql-server-authentication-mode-using-ssms\/","title":{"rendered":"Change SQL Server Authentication Mode Using SSMS"},"content":{"rendered":"\n<p>Authentication is a process that assists in the establishment of a secure connection. SQL Server provides two distinct authentication modes for connecting to database instances, as illustrated below:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Mixed Mode:<\/li>\n<\/ul>\n\n\n\n<p>This mode provides two distinct methods for successfully connecting to a database. While one makes use of SQL Server logins at the SQL Server layer, the other makes use of Windows domain users verified through active directory, where the login is generated and mapped to the SQL Server instance.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Mode Windows:<\/li>\n<\/ul>\n\n\n\n<p>This mode provides a single method for establishing database connections. Prior to creating database connections, we must map Windows accounts on SQL Server. Additionally, this mode disables access to SQL Server logins.<\/p>\n\n\n\n<p>Additionally, the authentication mode is configured during the SQL Server instance&#8217;s installation. Even so, after installation, we can change the SQL Server authentication mode.<\/p>\n\n\n\n<p>Additionally, when using Mixed mode, the encrypted SQL Server Authentication login password must be transmitted across the network during the connection process. Occasionally, applications will store the password on the client.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-change-the-authentication-mode-of-sql-server-using-sql-server-management-studio\">How to Change the Authentication Mode of SQL Server Using SQL Server Management Studio<\/h2>\n\n\n\n<p>The following steps describe how to change or set the authentication mode in SQL Server:<\/p>\n\n\n\n<p>1. To begin, open SSMS and establish a connection to the target SQL Server instance.<\/p>\n\n\n\n<p>2. Then, in the SQL Server Management Studio Object Explorer, right-click the server and select Properties.<\/p>\n\n\n\n<p>3. Following that, navigate to the Security page, where you&#8217;ll find both authentication modes listed under Server authentication. We can choose any option that meets our requirements and click OK.<\/p>\n\n\n\n<p>4. Following that, when a popup window prompts us to restart the SQL Server service, click Ok.<\/p>\n\n\n\n<p>5. At last, in Object Explorer, right-click the server and select Restart. If SQL Server Agent is also running, it must be restarted.<\/p>\n\n\n\n<p>If we are switching from Windows to Mixed mode, we must allow the SQL Server login sa account, as it is disabled by default in Windows authentication mode.<\/p>\n\n\n\n<p><strong>The SQL Server login sa account can be enabled by following these steps:<\/strong><\/p>\n\n\n\n<p>1. To begin, go to Object Explorer &gt; Security &gt; Logins and right-click sa, then select Properties.<\/p>\n\n\n\n<p>2. After that, on the General page, generate and confirm a password for the sa login.<\/p>\n\n\n\n<p>3. Then, go to the Status page&#8217;s Login section and click Allowed, then Ok.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-changing-authentication-mode-in-sql-server-through-t-sql-statement\">Changing Authentication Mode in SQL Server through T-SQL Statement<\/h3>\n\n\n\n<p>Microsoft provides an extended stored procedure that enables us to alter the SQL Server authentication mode using T-SQL statements. Before running the following script to change authentication mode to Windows mode, we suggest making a backup of the Windows registry:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>USE &#91;master]\nGO\nEXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',\nN'Software\\Microsoft\\MSSQLServer\\MSSQLServer',\nN'LoginMode', REG_DWORD, 1\nGO<\/code><\/pre>\n\n\n\n<p>Even so, If we want to switch to Mixed mode, we must first enable sa login. This can also be accomplished using the T-SQL statement:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER LOGIN sa ENABLE ;\nGO\nALTER LOGIN sa WITH PASSWORD = 'STRONG-PASSWORD' ;\nGO<\/code><\/pre>\n\n\n\n<p>Are you looking for an answer to another query? <a href=\"https:\/\/www.skynats.com\/contact-us\/\" target=\"_blank\" rel=\"noreferrer noopener\"><em>Contact<\/em><\/a> our technical support team.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Authentication is a process that assists in the establishment of a secure connection. SQL Server provides two distinct authentication modes for connecting to database instances, as illustrated below: This mode provides two distinct methods for successfully connecting to a database. While one makes use of SQL Server logins at the SQL Server layer, the other [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[693,692],"class_list":["post-9409","post","type-post","status-publish","format-standard","hentry","category-blog","tag-server-authentication","tag-ssms"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v25.9 (Yoast SEO v27.6) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Change SQL Server Authentication Mode Using SSMS<\/title>\n<meta name=\"description\" content=\"Learn how to change SQL Server authentication mode using SSMS step-by-step. Follow our guide and secure your server now!\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.skynats.com\/blog\/change-sql-server-authentication-mode-using-ssms\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Change SQL Server Authentication Mode Using SSMS\" \/>\n<meta property=\"og:description\" content=\"Learn how to change SQL Server authentication mode using SSMS step-by-step. Follow our guide and secure your server now!\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.skynats.com\/blog\/change-sql-server-authentication-mode-using-ssms\/\" \/>\n<meta property=\"og:site_name\" content=\"Server Management Services | Cloud Management | Skynats\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/skynats\" \/>\n<meta property=\"article:published_time\" content=\"2022-04-22T10:00:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-05-23T10:59:17+00:00\" \/>\n<meta name=\"author\" content=\"Kevin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@skynatstech\" \/>\n<meta name=\"twitter:site\" content=\"@skynatstech\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kevin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/change-sql-server-authentication-mode-using-ssms\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/change-sql-server-authentication-mode-using-ssms\\\/\"},\"author\":{\"name\":\"Kevin\",\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#\\\/schema\\\/person\\\/749ae0470320eb759ff1e07b8ea7fbe3\"},\"headline\":\"Change SQL Server Authentication Mode Using SSMS\",\"datePublished\":\"2022-04-22T10:00:37+00:00\",\"dateModified\":\"2025-05-23T10:59:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/change-sql-server-authentication-mode-using-ssms\\\/\"},\"wordCount\":476,\"publisher\":{\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#organization\"},\"keywords\":[\"server authentication\",\"ssms\"],\"articleSection\":[\"Blog\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/change-sql-server-authentication-mode-using-ssms\\\/\",\"url\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/change-sql-server-authentication-mode-using-ssms\\\/\",\"name\":\"Change SQL Server Authentication Mode Using SSMS\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#website\"},\"datePublished\":\"2022-04-22T10:00:37+00:00\",\"dateModified\":\"2025-05-23T10:59:17+00:00\",\"description\":\"Learn how to change SQL Server authentication mode using SSMS step-by-step. Follow our guide and secure your server now!\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/change-sql-server-authentication-mode-using-ssms\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.skynats.com\\\/blog\\\/change-sql-server-authentication-mode-using-ssms\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/change-sql-server-authentication-mode-using-ssms\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Change SQL Server Authentication Mode Using SSMS\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/\",\"name\":\"Server Management Services | Cloud Management | Skynats\",\"description\":\"Server Management and Cloud Management\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#organization\",\"name\":\"Skynats Technologies\",\"url\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/wp-content\\\/uploads\\\/2021\\\/08\\\/Sknats-Logo-New-whole.png\",\"contentUrl\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/wp-content\\\/uploads\\\/2021\\\/08\\\/Sknats-Logo-New-whole.png\",\"width\":989,\"height\":367,\"caption\":\"Skynats Technologies\"},\"image\":{\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/skynats\",\"https:\\\/\\\/x.com\\\/skynatstech\",\"https:\\\/\\\/www.instagram.com\\\/skynatstech\\\/\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/skynats-technologies\",\"https:\\\/\\\/www.youtube.com\\\/channel\\\/UCvTAjrFJ4_E2MJKwlDHomlg\"]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#\\\/schema\\\/person\\\/749ae0470320eb759ff1e07b8ea7fbe3\",\"name\":\"Kevin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/37a006382b218eff478403065cc9d903f85dd0085cb2af7fee95b4537b581c13?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/37a006382b218eff478403065cc9d903f85dd0085cb2af7fee95b4537b581c13?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/37a006382b218eff478403065cc9d903f85dd0085cb2af7fee95b4537b581c13?s=96&d=mm&r=g\",\"caption\":\"Kevin\"}}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Change SQL Server Authentication Mode Using SSMS","description":"Learn how to change SQL Server authentication mode using SSMS step-by-step. Follow our guide and secure your server now!","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.skynats.com\/blog\/change-sql-server-authentication-mode-using-ssms\/","og_locale":"en_US","og_type":"article","og_title":"Change SQL Server Authentication Mode Using SSMS","og_description":"Learn how to change SQL Server authentication mode using SSMS step-by-step. Follow our guide and secure your server now!","og_url":"https:\/\/www.skynats.com\/blog\/change-sql-server-authentication-mode-using-ssms\/","og_site_name":"Server Management Services | Cloud Management | Skynats","article_publisher":"https:\/\/www.facebook.com\/skynats","article_published_time":"2022-04-22T10:00:37+00:00","article_modified_time":"2025-05-23T10:59:17+00:00","author":"Kevin","twitter_card":"summary_large_image","twitter_creator":"@skynatstech","twitter_site":"@skynatstech","twitter_misc":{"Written by":"Kevin","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.skynats.com\/blog\/change-sql-server-authentication-mode-using-ssms\/#article","isPartOf":{"@id":"https:\/\/www.skynats.com\/blog\/change-sql-server-authentication-mode-using-ssms\/"},"author":{"name":"Kevin","@id":"https:\/\/www.skynats.com\/blog\/#\/schema\/person\/749ae0470320eb759ff1e07b8ea7fbe3"},"headline":"Change SQL Server Authentication Mode Using SSMS","datePublished":"2022-04-22T10:00:37+00:00","dateModified":"2025-05-23T10:59:17+00:00","mainEntityOfPage":{"@id":"https:\/\/www.skynats.com\/blog\/change-sql-server-authentication-mode-using-ssms\/"},"wordCount":476,"publisher":{"@id":"https:\/\/www.skynats.com\/blog\/#organization"},"keywords":["server authentication","ssms"],"articleSection":["Blog"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.skynats.com\/blog\/change-sql-server-authentication-mode-using-ssms\/","url":"https:\/\/www.skynats.com\/blog\/change-sql-server-authentication-mode-using-ssms\/","name":"Change SQL Server Authentication Mode Using SSMS","isPartOf":{"@id":"https:\/\/www.skynats.com\/blog\/#website"},"datePublished":"2022-04-22T10:00:37+00:00","dateModified":"2025-05-23T10:59:17+00:00","description":"Learn how to change SQL Server authentication mode using SSMS step-by-step. Follow our guide and secure your server now!","breadcrumb":{"@id":"https:\/\/www.skynats.com\/blog\/change-sql-server-authentication-mode-using-ssms\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.skynats.com\/blog\/change-sql-server-authentication-mode-using-ssms\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.skynats.com\/blog\/change-sql-server-authentication-mode-using-ssms\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.skynats.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Change SQL Server Authentication Mode Using SSMS"}]},{"@type":"WebSite","@id":"https:\/\/www.skynats.com\/blog\/#website","url":"https:\/\/www.skynats.com\/blog\/","name":"Server Management Services | Cloud Management | Skynats","description":"Server Management and Cloud Management","publisher":{"@id":"https:\/\/www.skynats.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.skynats.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.skynats.com\/blog\/#organization","name":"Skynats Technologies","url":"https:\/\/www.skynats.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.skynats.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.skynats.com\/blog\/wp-content\/uploads\/2021\/08\/Sknats-Logo-New-whole.png","contentUrl":"https:\/\/www.skynats.com\/blog\/wp-content\/uploads\/2021\/08\/Sknats-Logo-New-whole.png","width":989,"height":367,"caption":"Skynats Technologies"},"image":{"@id":"https:\/\/www.skynats.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/skynats","https:\/\/x.com\/skynatstech","https:\/\/www.instagram.com\/skynatstech\/","https:\/\/www.linkedin.com\/company\/skynats-technologies","https:\/\/www.youtube.com\/channel\/UCvTAjrFJ4_E2MJKwlDHomlg"]},{"@type":"Person","@id":"https:\/\/www.skynats.com\/blog\/#\/schema\/person\/749ae0470320eb759ff1e07b8ea7fbe3","name":"Kevin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/37a006382b218eff478403065cc9d903f85dd0085cb2af7fee95b4537b581c13?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/37a006382b218eff478403065cc9d903f85dd0085cb2af7fee95b4537b581c13?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/37a006382b218eff478403065cc9d903f85dd0085cb2af7fee95b4537b581c13?s=96&d=mm&r=g","caption":"Kevin"}}]}},"_links":{"self":[{"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/posts\/9409","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/comments?post=9409"}],"version-history":[{"count":1,"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/posts\/9409\/revisions"}],"predecessor-version":[{"id":14946,"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/posts\/9409\/revisions\/14946"}],"wp:attachment":[{"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/media?parent=9409"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/categories?post=9409"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/tags?post=9409"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}