I am pretty new at sql, andt this problem is nagging me. I have an instance of SQLEXPRESS on a computer in a local network. Users from remote computers in the LAN can access the database without problems, unless they log in with a Microsoft account. In fact what happens is that if I log in with my ms account, using pin or password, and try to connect to the server with SSMS (version 18.7), I get the error
The target principal name is incorrect. Cannot generate SSPI context
If, on the same computer, I switch user and log in with a local account, everything works fine. As I said, I am not very experienced: maybe someone can explain what is happening and what I might try to solve the problem. Thank you in advance
This looks to be a Kerberos authentication issue - when you login to the domain and then attempt to access SQL Server, your system is trying to authenticate with the Kerberos ticket.
The SPN (Service Principal Name) associated with that instance of SQL Server is not setup correctly, causing the SSPI context error.
You need to get with your network team and have them fix the SPN - or remove it for that instance.
Thank you for your interest. Maybe I should make myself more clear. I wish I could contact my network team: the truth is that I am the network team. The sql application is a small one, derived from an original excel project, evolved first to access and then to access frontend with sql backend, with the target of automating the procedures for membership and accounting of a local association, on a purely voluntary basis. I did practically everything, and, so to speak, I am learning on the job. In my (in)experience, I never used Kerberos, and we did not set up a domain, so maybe this is the main problem. Should I fix that? and how do I fix the SPN? Sorry for the naive questions...Also, can you have a domain without windows server?
1.What permisson have you configured on the sql server that allows random users in the network to be able to access the sql server?
2. How are they accessing the sql server?
3. What do you mean when yoi say when they login to their microsoft account, which microsoft account.
4.when they were able to access sql server what were they logged in into?
You can use Ubuntu Server/Samba as domain server
We are dealing with a LAN of 8 pc and 4 printers, behind a vodafone station router. All pc's have windows 10 pro installed.
One pc (PC1) hosts the sql server (SQLEXPRESS) containing essentially two databases, one for access control and electronic card handling (provided by an external firm) and one for membership and accounting (my "creation").
The server is accessed by the other computers remotely. The users are always logged in with local account: as I said, no problems in connection. This is the DNS file used for the connection (every pc has its own copy of access frontend):
[ODBC]
DRIVER=SQL Server Native Client 11.0
DATABASE=RicevuteCPNVersion2
WSID=PC0X
APP=Microsoft Office 2016
Trusted_Connection=Yes
SERVER=PC01\SQLEXPRESS
Description=RicevuteCPN2conCorsi
where PC0X is the user desktop, and the server is on PC01.
I use windows trusted connection. Note that I left most of the configuration at default, with the intention of refining it later, but the permission policy still baffles me a lot. Anyway, the permissions are
(for sql server)
BUILTIN\user
PC01\Guest
PC01\User
public
(for my database)
PC01\Guest
public
The error only happens when logging in with any Microsoft account. For example, my MS account is gandalfrp@tiscali.it, pwd=xxxxxxxx. I can also define a 4 digit PIN for faster login. It is true that, as long as I can access the server using a local account, the problem is not that urgent, but I would like to understand. I was (wrongly) convinced that, once you are logged in, the PC01\Guest permission could take care of everything. Again, sorry for my naivete and feel free to delete this post if too stupid.
Logging in to the pc with microsoft account?
Yes.Something wrong with that? At the moment, at home, I am logged in with MS account. When you install Windows, this is one of the option you have: of course, you may refuse and define a local accont, but I don't see anything wrong with it. Of course, again, I may be grossly mistaken
no, just asking what that meant. I never tried that before. None of the stuff you are asking is stupid. and no one will delete your posts. it is ok to ask anything.
so how about this. what if you used a SQL Login instead of the PC/Guest accounts?
1 Like
Why didn't I think of that? Yes, it works. Actually, the first time did not work: could not open default database. So I opened the SSMS "options" in the Connect mask and wrote the name of my database, and this time the connection went ok. Thanks: I still don't understand why it doesn't like the windows trusted connection, but one step at the time...
I believe it it is what @jeffw8713 had mentioned earlier. once you login using a microsoft account, now someone from a different domain (not local anymore) is attempting to access a resource (your sql server) in a different domain (local pc based) make sure you give that SQL user just enough perms, don't give it db owner permissions, maybe just read/write permissions
Not sure about that. Because of the COVID, at the moment the association is closed, I am connected to the lan computers from home using Anydesk and I am for sure the only one working. So it is very unlikely that someone is attempting to access our sql server.
I am talking about you and your other users, when logged in using microsoft login, you are from a different domain microsoft domain.
It sounds like you have not setup a domain - or even a workgroup on those PC's. Every user is logging into their PC using their Microsoft account.
In that situation - when attempting to login using Windows Authentication, SQL Server is going to try and authenticate the user based on their windows credentials - which is actually coming from a Microsoft domain. SQL Server doesn't know anything about that domain and cannot access the domain to validate the user.
Using SQL Authentication is one way around this - and probably the only way at this point. But - your security on that system needs to be updated. You should not be granting access to Public or Guest as that opens your system up to anyone who can make a connection.
You should have a separate SQL login for each user that needs to access the database. That login would then be mapped to a separate user in the database with appropriate permissions for that user. If all users need the same permissions - you can setup a role - and grant (assign) each user to that role.
2 Likes
Wait a sec. I did not set up a domain, thought it was not necessary in our amateurish situation, we do not even have a server (however, all PC's are in WORKGROUP workgroup). I said in the other post that all users can connect and work on the database without problems, because they log in on their pc's with a LOCAL account. In fact, I was not aware of this problem before: now, working by myself from home, I created an account on one PC of the LAN to run some tests, and I created the account as I have here at home, i.e. a MS account: and, voilà, I got the error. So you are saying that I am the cause of the error, since my login has an implicit domain (Microsoft). Nice, at last this is clear now. Thanks again.
Now, assuming everyone will continue to use a local account, do I still need to switch to sql authentication? Assuming the local account are something like WN02, WN03, etc, can I just remove the Guest and public and define a separate login for each of these (with proper permissions), instead of defining an SQL userid for each? I could still access with sql authentication and all privileges.
I need to refine the access security, and I have to study a littlke bit more. Thanks a lot for your suggestions.
Yes, you can continue with the local accounts, which will be the same as using SQL accounts.
the only issue with that approach is it not sustainable. if tomorrow a new pc comes online or your company just got bought up and you have 30, 50 new PCs, you going to have to go in and tweak stuff manually. no sustainable and not best practice. my 2 cents
I don't think I will have those problems, but your 2 cents suggestion is right on target. Even if the users now are about 4-5, I don't like the idea of providing separate accesses to database with essentially the same permissions. Now the access is via Guest, I believe, but as Jeff says, this is not safe: everyone who gains access to the pc hosting sql can access the database.
I should group all users under only one database login. Can I do that? Do I need sql authentication for that? Definitely, I need to study a little bit more. Thank you to all.
If you use SQL authentication - every user would retain the same login information regardless of PC. At this time - all users are setup the same and require the same permissions, but what happens when you need to add users for read only access...or users that have limited access - or even users that require more access?
The problem with guest is not that someone can get access if they are on that PC - but that anyone on your network has access. Anyone can open Excel and create a connection...