24

On connection pop up click on options Then make sure the "Trust server certificate" is checked


8

If you're only using TCP/IP to connect to your instances, you only need the ports specified. The Instance Names are used when connecting to the SQL Instances via the Named Pipes protocols. Sadly the MS article doesn't come right out say which format is required for which protocol, but it is derived from (many tests in my environment) and the following MS ...


6

I don't know if you got this resolved or not, but after recently going through our own (re)discovery of the requirements to get delegation running, I figured I could at least provide an answer in the event others come across your post with similar questions. To help make my post clear (especially to anyone coming across it and not reading through your ...


5

What would be the drawbacks of telling my developers to use a DNS entry to point to their respective databases in their ConnectionStrings? Technically, now, one of the drawbacks is that they aren't going to change their connections strings... when, say, they should be adding in connection specific keywords such as MultiSubnetFailover. You'll also have a ...


5

go advanced "options" and put TrustServerCertificate=True to Additional Connection Parameters


5

To create a SPN for a SQL FCI, use the FQDN of the FCI instance. For example, if the FCI name is "SQLFCI1" on the contoso domain and it listens on port 22000 with domain account SQLSvcAcct then the spn would be: setspn -s MSSQLSvc/SQLFCI1.contoso.com:22000 Contoso\SQLSvcAcct If you don't want to deal with doing this by hand there is a great tool provided by ...


5

An Active Directory domain is an organizational unit within a Forest. A forest is simply a collection of domains that allows the domains be managed as a unit. Consider an SPN as a domain or forest unique identifier of some instance in a server resource. The above means the SPN (service principal name) should be unique across the entire organization. ...


5

You need to talk to your network admin to see if Kerberos delegation is enabled, and whether it's constrained or not (if it's enabled and unconstrained that is a massive potential security risk, just by the by). If it is constrained then you need to register a SPN (Service Principal Name) for your SQL Server and bind your SQL Server account service to that ...


5

This is known as the "double hop" issue between SQL Server and IIS. It looks like you may not have trusted the IIS server for delegation. Here's a good checklist of things to do when setting up SQL Server with IIS using Kerberos. Here's a great primer on the condition. The anonymous login is a give-away of the problem.


4

Here's the way I see it. When you use a VA, you impersonate the machine account. The problem is, that it is easy to make a VA or use an existing one (ex. NT Authority\NETWORKSERVICE). If you grant the machine account access to an instance, an application that is running as a VA will be able to connect to that instance and perform actions. With a managed ...


4

This is an issue with Kerberos Authentication. You are connecting to SQL Server on your own laptop, but that service runs under a different process & account than your SSMS session. Then, that SQL Server instance needs to connect across the linked server. That's one hop from SSMS to SQL Server, then a second hop to the second SQL Server instance. Doing ...


3

There is only a single kind of Service Principal Name you need for SQL Server. MSSQLSvc/<servername>:<portname> It is typically necessary to implement this twice, one for the short name, and one for the long name. If, for instance, the SQL Server is named "SQLServer1.somedomain.com" and was running on port 1433, then you'd need: MSSQLSvc/...


3

how can this be that while "Write ServicePrincipalName" privilege is turned off for the SQL service account, domain users are connecting using Kerberos auth That privilege is not required for Kerberos to work, it only enables the service account to register its own Service Principal Names (SPNs). See Register a Service Principal Name for Kerberos ...


3

Quick answer Question: I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment. Is there a way of doing this? Answer: No. Explanation From the official documentation Register a Service Principal Name ...


3

Finally figured this out! We reference many of our very active SQL Servers and Instances with SQL aliases and canonical names. This allows us to perform parallel upgrades and switch machines in and out without code interruption. The SQL aliases are all configured to reference the canonical names for the same reason. The standard SPNs we had registered ...


3

The error message you're seeing, 0x80090350, is defined as: c:\util\Err>err 0x80090350 # for hex 0x80090350 / decimal -2146892976 : SEC_E_DOWNGRADE_DETECTED winerror.h # The system detected a possible attempt to compromise # security. Please ensure that you can contact the server # that authenticated you. # 1 ...


3

If your SQL Servers are clustered, and their service accounts are configured to allow them to manage their own Service Principal Names, you might be running into a known issue. In an environment with physically distanced Domain Controllers, a planned failover event can cause the Kerberos delegation for double-hop authentication to fail with symptoms ...


2

Additional reading on troubleshooting Kerberos errors can be found at the following links Troubleshooting Kerberos Errors (whitepaper) alternate link, wayback machine Debugging Windows Authentication Errors


2

When the SQL Server service creates SPN, it creates two for each instance. This is the format it uses. Default Instance: MSSQLSvc/servername.domain.com MSSQLSvc/servername.domain.com:1433 Named Instance: MSSQLSvc/servername.domain.com:54321 MSSQLSvc/servername.domain.com:instancename For your named instances, if creating SPNs manually, you will need to ...


2

This currently is not available yet for SQL Server 2016 unfortunately. You will have to manually add your SPN's using setspn –S MSSQLSvc/<server name>:1433 domain\account EDIT: The Kerberos Configuration manager is now available for SQL Server 2016 https://www.microsoft.com/en-us/download/details.aspx?id=39046


2

I think that you missed one step. You should add an entry into the keytab file for your principal mongodb/mongodb.centos7.vm@CENTOS7.VM Suppose that the path for your keytab file is /etc/krb5.keytab. Now run $ktutil ktutil: add_entry -password -p mongodb/mongodb.centos7.vm@CENTOS7.VM -k 1 -e des-cbc-md4 ktuitl: wkt /etc/krb5.keytab ktuitl: quit Now you ...


2

Yes, a SPN is simply telling the client that when it connects to the specified service (MSSQLSvc) on the specified host (shortername.mydomain.com), the specified principal (MYDOMAIN\SQLSvcAccount) will be the owner of the process that it is connecting to. This ensures that the connection will fail if DNS is wrong and it ends up connecting to a process owned ...


2

Kerberos Authentication requires that you have Service Principal Names registered for the services being run by your service account to perform the exchange required for Kerberos authentication to work. You can easily validate your SPNs using Microsoft's Kerberos Configuration Manager. Once you've validated and fixed any SPN discrepancies, confirm if your ...


2

I can think of at least two ways this could be explained. Someone with adequate rights manually created the SPNs for instances 3 and 4. Instances 3 and 4 ran at least once under a service account that had enough rights to create the SPNs, even though the service account they currently use does not have adequate rights to do so.


2

When you launch your app using "runas" command, you use some domain user, and that user has Windows login at your SQL server. Or that domain user is part of some AD group that has Windows login at your SQL Server When you launch app without "runas" command, the app is running, but under different security context (under different windows user), right ? In ...


1

When the auth_scheme is SQL, that means the user is using SQL authentication, which does not, and cannot use Kerboros. When a user that is using Windows authentication connects, authentication will first try to use Kerberos. If Kerberos fails for some reason, authentication will fall back to NTLM. Probably the most common cause of Kerberos failure is that ...


1

Verify that both SQL Servers can use Kerberos connect from your laptop to SQLSERVER1 using windows auth. Run this query: select auth_scheme from sys.dm_exec_connections where session_id = @@spid It should return "Kerberos". If it returns "NTLM": Verify no duplicate spn's (SETSPN -X) Verify correct spn's (SETSPN -L) Repeat for SQLSERVER2 Once you can ...


1

The only way you can pass Windows Credentials between servers to my knowledge is via delegation as you've already taken a look at. If there's truly no way to get delegation working in your environment, an alternative to delegation is to fall back to mapping your Domain accounts to SQL authenticated accounts on the remote server that are setup with ...


1

Having no SPN does not cause this error. Having an invalid SPN does. It's not enough to make sure an SPN exists, you have to make sure it is owned by the SQL Server service account and that it is configured correctly. Follow the advice I blogged here: http://sqlsoldier.net/wp/sqlserver/advanced-troubleshooting-week-at-sql-university-lesson-1


1

Assuming the client has SSMS 2012 installed, you might have one of them try running the following from a command-prompt as a test: %SystemRoot%\System32\runas.exe /netonly /user:DOMAIN\Username "%ProgramFiles(X86)%\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\ssms.exe" If they have some other version of SSMS, you'd need to replace the "110" with ...


Only top voted, non community-wiki answers of a minimum length are eligible