locked
Why am I having so many problems with database files, ect ...??!!?? RRS feed

  • Question

  • I am at my wits end with permissions, accounts, instances, db local vs express vs server management, code first vs data first vs who's on first ... I don't know what all, error messages, ect ... AAARGG!!  Every tutorial or knowledge base I go to read or watch, its something different every time.  It seems to me there is at least 100 different ways to do the same thing and none of 'em are ever quite working out.

    All I want to do is what I'm supposed to be able to.  Create a simple little database or use an existing one and get it to incorporate into a simple little public facing website that I can host myself using IIS over a simple little laptop but all I'm getting are problems.  I'm trying to use Visual Studio to whip up some kind of website that I can write myself using C#/.net.  Time after time after time I get error messages when working with SQL no matter if its db local, express or whatever demons are left. 

    What in the hell is MSSQL$SQLEXPRESS01!!  Is it some built in secret account that some little guy like me isn't supposed to be able to figure out.  What is NetworkService, LocalService or System other than just more confusion.  What is sa which requires a password?  What does logging into a server engine even mean?  Where do local users fit in to all of this?  How does the server management program work with Visual Studio?  Where should database files be stored and what kind of permissions are required to use any of them?  Are they supposed to work together or is all just so convoluted that even a bright dummy like me isn't supposed to be able to figure out?  What the hell?  Can somebody here tell me what's going on?  Any takers?

    Wednesday, April 1, 2020 5:57 AM

Answers

  • Hi markchiffon,

    >What is NetworkService, LocalService or System other than just more confusion?
    1. The Network Service account is a built-in account that has more access to resources and objects than members of the Users group. 
    2. The Local Service account is a built-in account that has the same level of access to resources and objects as members of the Users group. 
    Please reference : configure-windows-service-accounts-and-permissions

    >What is sa which requires a password?
    sa" is to SQL Server what "Administrator" is to Windows.
    Please reference: authentication-access/server-level-roles

    >What does logging into a server engine even mean?
    It means you have connect one instance successfully. And then you can start your SQL trip.
    Please reference: install-sql-server-database-engine ,connecting-to-the-database-engine

    >How does the server management program work with Visual Studio?
    Please refrence: sql-server-develop-use-vscode

    >Where should database files be stored and what kind of permissions are required to use any of them?
    Default path: C:\Program Files\Microsoft SQL Server\MSSQLn.SQLxxxx\MSSQL\DATA
    Permission: configure-file-system-permissions-for-database-engine-access

    Please reference: working-with-databases
    If you have any issues about the SQL Server, you can post them in this forum.

    Best Regards.
    yuxi 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by markchiffon Thursday, April 2, 2020 1:48 AM
    Wednesday, April 1, 2020 8:32 AM
  • Hi markchiffon,

    Is the reply helpful?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by markchiffon Thursday, April 2, 2020 3:05 AM
    Thursday, April 2, 2020 1:04 AM

All replies

  • Hi markchiffon,

    >What is NetworkService, LocalService or System other than just more confusion?
    1. The Network Service account is a built-in account that has more access to resources and objects than members of the Users group. 
    2. The Local Service account is a built-in account that has the same level of access to resources and objects as members of the Users group. 
    Please reference : configure-windows-service-accounts-and-permissions

    >What is sa which requires a password?
    sa" is to SQL Server what "Administrator" is to Windows.
    Please reference: authentication-access/server-level-roles

    >What does logging into a server engine even mean?
    It means you have connect one instance successfully. And then you can start your SQL trip.
    Please reference: install-sql-server-database-engine ,connecting-to-the-database-engine

    >How does the server management program work with Visual Studio?
    Please refrence: sql-server-develop-use-vscode

    >Where should database files be stored and what kind of permissions are required to use any of them?
    Default path: C:\Program Files\Microsoft SQL Server\MSSQLn.SQLxxxx\MSSQL\DATA
    Permission: configure-file-system-permissions-for-database-engine-access

    Please reference: working-with-databases
    If you have any issues about the SQL Server, you can post them in this forum.

    Best Regards.
    yuxi 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by markchiffon Thursday, April 2, 2020 1:48 AM
    Wednesday, April 1, 2020 8:32 AM
  • Hi markchiffon,

    Is the reply helpful?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by markchiffon Thursday, April 2, 2020 3:05 AM
    Thursday, April 2, 2020 1:04 AM
  • Thank you yuxi for your reply.  I really appreciate it.  I have already been through before most of the pages you listed.  Coming at it from a SQL for dummies kind of angle those pages give me information overload and picking out of it what seems to apply to my little situation has been difficult.

    For instance, take this just one example.  I recently found what I thought was a good tutorial to follow on youtube.  It uses studio management to create a small database with a few tables with a few one-to-many relationships.  In the video its obvious the database file is being put into a folder that is not the "default" location.  The video moves to the VS project and uses the add "ado.net entity data model" option then selects "EF Designer From Database".  The wizard then walks you through setting up the already existing database.  You're supposed to be able to browse to the database file you just created where ever that may be, select it and then VS is supposed to scaffold all the model classes and contexts and incorporate it into the project.  Right?  You follow me?  I get errors about file already in use or lacks permissions or UNC shares violations of some sort.  These are troubles that I have encountered before.  It never goes smoothly if I can even get the database file to be recognized.  It all seems to revolve around ownership, permissions, log in issues which I can't seem to figure any of it out or make any of it work.  Could it be that SQL Express was not installed correctly?  or I'm supposed to do everything under my administration account and not as a local user?  Does studio management need different permissions or accounts than does VS or even IIS?

    I realize these may be questions you can't specifically answer.  Could I possibly try things again and pass along error messages as I get them and bring them here to this forum and maybe you could help me resolve them?  Or is there another forum somewhere that would work better? I think now I definitely need outside help to get around these seemingly many issues. Why shouldn't I be able to put a database file where ever I want. The video did. But I'm running into all sorts of problems. Trying to get a database incorporated into my VS projects is proving to be difficult for some reason.

    Thursday, April 2, 2020 1:38 AM
  • Hi markchiffon,

    Yes, if you encounter problems about SQL Server again, you can ask questions here, and you say you are learning from one video, and if it is possible, you can post the link of the course, and I will follow that again and test that again and then reply your question. Please be feel free to post in this forum , and there will be MVPs and engineers reply your post ASAP, if the issue is not SQL, we or they will give you the relative forum link which will suit for your issues.Please go on your learning and do not hesitate post issues here.

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by markchiffon Saturday, April 4, 2020 3:39 PM
    • Unmarked as answer by markchiffon Wednesday, April 8, 2020 1:07 AM
    Friday, April 3, 2020 1:42 AM
  • Okay thanks.  The video I have been following is "a" video that I'm trying.  One of many.  I'm not just trying to learn from "one" video.  I've been all over the place and SQL obviously along with Visual Studio and IIS is not exactly what I'd call user friendly.  But I understand they are extremely flexible programs that try to meet everybody's needs which I'm sure is incredibly varied.  I sense that my difficulties are maybe simple and once I get the hang of whatever is hanging me up, I'll be the first to admit my boneheadedness.  But I've pretty much made up my mind that these programs and documentation are not very intuitive which is why I'm here now.

    Let me try this first which is for whatever reason upmost on my mind.  I am using Windows 10 as a local user.  Very seldom do I log into Windows under my Administrator account.  So my question is should I be using SSMS and/or VS as a logged in administrator or can I continue as a local user? 

    With IIS you MUST enter admin credentials to interact with its management program.  I'm pretty sure it serves web pages even if the OS is currently running under a local user account.  Hell, I'm thinking it better be.  Why should I have to be logged in as an administrator in order for IIS to work behind the scenes?  Wouldn't that make my system more vulnerable?  SQL says you should use SQL with the lowest privileges possible which I'm not totally sure what exactly that means.  SQL runs behind the scenes as well, right?  So if I'm logged into my OS as a local user then that seems to me as limited of privileges that I could possibly give it.  Could I be running into problems trying to do things as a local user?

    In the meantime while you respond to this one question I will be trying to reproduce some error messages along with, as best and briefly as I can, the sequence of steps leading up to them.  I realize I'm being very wordy right now but, like I've said, I'm at my wits end with all this and I'm just trying to put you in my seat so you can see what I'm been seeing.

    Again, thanx for any help at all.

    Saturday, April 4, 2020 2:53 PM
  • So nothing?  Hmmmm. 

    We'll this is what I am experiencing. 

    It looks to me like I should "run as" administrator SSMS to create a database.  If I direct the database files to another folder other that the default I should make sure that "NetworkService" has permissions all along that path including my local user permissions.  Apparently this is because the database engine is logged in as NetworkService?  So now it seems that I also should make sure I "run as" administrator Visual Studio to incorporate the database files into the project. 

    It appears to me that SSMS only creates database files happily for members of the administrator group using Windows Authenication and not for local users logged into the OS and that must include trying to set up VS with the same database files.

    Once I did that I've found that I can return to running VS as a local user on the OS and access the database files where I put them with SSMS.  It appears that might work for me.  I don't see any reason why I should be logging into my OS or running Visual Studio as an administrator in order to play around with Visual Studio.  Doesn't that reduce my protections? 

    So there you have it.  Maybe I've found a way to continue on with SSMS and VS as a local user into my OS which is Windows 10 Home edition.  Could it be that it is the lack of being able to control user groups with the Home edition as to these problems that I've been experiencing? 

    If this can continue to work for me, I guess the only thing remaining is if IIS is going to be happy with any of this.

    So there you have it.  I haven't done this enough times to develop a pattern but I will be trying to do it this way from here on out.  See if that works.  Thanks again.  I understand.

    Wednesday, April 8, 2020 1:49 AM