Announcement

Collapse
No announcement yet.

Win 2008 and Sql Express

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Win 2008 and Sql Express

    Hey guy's i have a win2008 machine with sql 2005 express. I have a domain user , a normal domain that belongs to the domain users. I have the invoice program installed, and what happens is that the software cannot connect to the sql database and says that access denied. How can i grant access to this user to use that sql instance?

    Regards
    Kevin Guerreiro

  • #2
    Re: Win 2008 and Sql Express

    Is the SQL server configured for mixed mode authentication or SQL authentication?

    Does the application use Windows authentication or SQL authentication?

    Is the SQL server configured for network connectivity? If so, which protocols are enabled?

    Comment


    • #3
      Re: Win 2008 and Sql Express

      Hey joe thx for the answer.

      1 - I don't know much about sql, so i dont really know if it's configured for mix mode or authentication mode, where can i see it ? ( Just found this http://msdn.microsoft.com/en-us/library/ms188670.aspx ), and it's sql and windows auth mode.
      2- If i'm not wrong, the applications uses sql Authentication, because when i connect the program to the database, it askes the instance and the SA password.

      3- the SQL instance is installed as a Local System and not network or local service.

      When i use the program without beeing loged on into the domain, the program works.

      Kevin
      Originally posted by joeqwerty View Post
      Is the SQL server configured for mixed mode authentication or SQL authentication?

      Does the application use Windows authentication or SQL authentication?

      Is the SQL server configured for network connectivity? If so, which protocols are enabled?
      Last edited by kevinguerreiro; 18th June 2009, 17:33.

      Comment


      • #4
        Re: Win 2008 and Sql Express

        OK, so it sounds like the application uses SQL authentication, which SQL uses by default so that's probably not the problem.

        Have you got an ODBC connection set up for the application?

        Does the sa user have access to the database?

        Have you run the SQL Server Configuration Manager to enable the network protocols?

        Comment


        • #5
          Re: Win 2008 and Sql Express

          Well, i dont have a ODBC connection setup for the application. The user sa has access i think, because when i go to Sql Express Manager i select the instance select sql auth, username sa and the password and can surf throw the database with no problem. All the protocola are active, TCP , Shred Memory and named pipes are active, and the tcp port i manualy selected 60104.

          Kevin

          Comment


          • #6
            Re: Win 2008 and Sql Express

            The port may be the problem. the native SQL port is 1433 and the application may not have the intelligence to determine the port if you're not running the SQL browser service on the server. Try changing the SQL server port to 1433 and see if that fixes it.

            If that doesn't fix it then the next step is to create an ODBC connection on the application computer.

            Comment


            • #7
              Re: Win 2008 and Sql Express

              Well i went to the sql configuration manager, selected the instance and went to tcp properties and changed the tcp port, not everything is the same.

              It Gives this error:

              -2147201005-Access Denied
              Line Number:328
              <ModMain:Main>

              How do i make a ODBC connection ?

              Regards
              Kevin

              Comment


              • #8
                Re: Win 2008 and Sql Express

                After you changed the port did you stop and restart the SQL Server service or reboot the SQL server?

                Comment


                • #9
                  Re: Win 2008 and Sql Express

                  Correct and Afirmative. How about if i add permitons of that user in particular in the sql folder mssq.2 ? It's strange, or not, but when the client computer is logon locally without the domain, the sql works perfectly, when you connect the user throw AD the sql connection doesent work.
                  Last edited by kevinguerreiro; 18th June 2009, 18:45.

                  Comment


                  • #10
                    Re: Win 2008 and Sql Express

                    NTFS permissions shouldn't have any affect AFAIK. SQL connections use TDS (tabular data stream) to connect clients to the server\database and not RPC or SMB so the permissions shouldn't have any bearing. Does the application connect to any file shares?

                    Comment


                    • #11
                      Re: Win 2008 and Sql Express

                      Well, in sql mode it goes directly to sql, but when i use mdb database instead of sql, i share the program folder and it works, dont know if i answered your question.

                      Comment


                      • #12
                        Re: Win 2008 and Sql Express

                        Oh, so the application connects to a SQL database and/or an MDB (MS Access) database? If that's the case then NTFS permissions will affect the connection to the MDB database.

                        Take a look at the permissions in the path to the MDB database and make sure that the Windows user that the apllication authenticates as has appropriate NTFS permissions.

                        Comment


                        • #13
                          Re: Win 2008 and Sql Express

                          joe, it can work in one way, or another, not both at the same time, so if it's using sql, it wont go to the folder to use the mdb file. It's very anoying why cannto connect to sql database when connected to domain, and when not connected to domain it works like a charm hmmm any extra ideias ?

                          Comment


                          • #14
                            Re: Win 2008 and Sql Express

                            Can you give me more details. you mentioned two things:

                            1. It can make a connection to SQL. I'm assuming this to mean that it makes an ODBC connection to the SQL database as SA and works fine.

                            2. It can make a connection to the MDB file. I'm assuming this to mean that it makes a file connection to a Microsoft Access database (MDB file) and this does not work fine.

                            I'm getting a little confused as to what you have and what the application is doing because you mention that it makes a connection to the MDB file and also makes a connection to SQL. MDB files are Microsoft Access database files and you typically connect to them or open them from the file system. A SQL database, on the other hand, is an MDF file and you typically connect to them or open them via an ODBC connection. A SQL connection via ODBC never touches the actual MDF file or the file system.

                            A SQL connection via ODBC using SA and SQL authentication never relies on file system permissions or Windows authentication so a true SQL connection would not matter whether you were logged on locally or logged on to the domain.

                            does the application make any kind of file connection to the SQL server?

                            Comment


                            • #15
                              Re: Win 2008 and Sql Express

                              Hello Joe, sorry if i'm not clear in what i say. Ill try to explain everything.

                              The software i have has two possible ways to work, or i select to use MDB database, and share the MDB database in the network by sharing the programs folder thro the network, or i select an sql database and dont share no Folder, and the program goes directly to the SQL Instance, it never uses both at the same time.

                              I have my win 2008 server that is acting as DC with kci.local domain, with sql 2005 expess instaled. I have installed the software in the server and connected the software to the sql instance with no problem and allready working. I have a Reception computer that wasent connected to the domain, just using a local account and installed the software and installed the software and connected to the sql instance with no problem and worked ok. Now the problem is when i setup the reception computer to join the domain kci.local, i login with the empoyer's account, and when i try to use the software, it say access denied to the sql instance.

                              Regards
                              Kevin Guerreiro
                              Last edited by kevinguerreiro; 19th June 2009, 13:00.

                              Comment

                              Working...
                              X