26 Replies Latest reply on Aug 12, 2008 7:56 AM by bregent

    Logon to a SQL Server

    Gold-bug
      Hi, All,

      I have two basic ASP pages and I'm trying to connect to a SQL Server 2005 Database which is on a different computer (Server-001).
      The first file is called TestTwo.asp and is in the root/folder: Connections

      It connect is below:

      <%
      ' FileName="Connection_odbc_conn_dsn.htm"
      ' Type="ADO"
      ' DesigntimeType="ADO"
      ' HTTP="false"
      ' Catalog=""
      ' Schema=""
      Dim MM_TestTwo_STRING
      MM_TestTwo_STRING = "dsn=Hagis SQL Server;uid=LoginName;pwd=Password;"
      %>

      A very basic webpages called LoginOne.asp has the following code:

      <%@LANGUAGE="VBSCRIPT"%>

      <!--#include file="Connections/TestTwo.asp" -->

      <%
      Dim Recordset1
      Dim Recordset1_numRows
      Set Recordset1 = Server.CreateObject("ADODB.Recordset")
      Recordset1.ActiveConnection = MM_TestTwo_STRING
      Recordset1.Source = "SELECT * FROM dbo.BullionProductName"
      Recordset1.CursorType = 0
      Recordset1.CursorLocation = 2
      Recordset1.LockType = 1
      Recordset1.Open()
      Recordset1_numRows = 0
      %>

      <style type="text/css">
      <!--
      body {
      background-color: #FFFFE6;
      }
      .style1 {
      font-size: 14px;
      font-family: Geneva, Arial, Helvetica, sans-serif;
      }
      -->
      </style>
      <title>Hagis Net</title>

      <%
      Recordset1.Close()
      Set Recordset1 = Nothing
      %>


      Anytime I put something within the statement between: <% %> my page shows not the webpage in the browser but directly the code itself. It seems that not only the login script when I put it between these: <% %>, but also the rest doesn't work out well.

      I think that the TestTwo.asp page is OK, because when I run a test within Dreamweaver MX 2004 the Recordset gives the SQL record and its content. So testing within Dreamweaver has a link/connection with my server and has the right authority, hence the correct UID and PWD.

      May anyone could make sense of it.
        • 1. Re: Logon to a SQL Server
          bregent Most Valuable Participant
          Is this on your local or remote server? Does the server support ASP? Do you have a local web server and is it configured properly?
          • 2. Re: Logon to a SQL Server
            Gold-bug Level 1
            Hi there,

            to all your questions I can see Yes. Currently I'm running a full intranet with 100+ webpages and ASP pages on a remote server in a local network. The webpage connect to a seperate disk with an ACCESS Database. All this works, so connections, script and routing seems OK.

            I'm switching from MS Access to MS SQL server 2005. Dreamweaver properly makes the connection, but the pages that I copied from my website that had a correct connection with de Access Dbase now doens't connect to MS SQL, although Dreamweaver seems to have made the right connection.

            Hope this helps.

            Looking forward to hear from you,

            Carl.
            • 3. Re: Logon to a SQL Server
              Level 7

              "Gold-bug" <webforumsuser@macromedia.com> wrote in message
              news:g7aafo$c2t$1@forums.macromedia.com...
              > Hi, All,
              >
              > I have two basic ASP pages and I'm trying to connect to a SQL Server 2005
              > Database which is on a different computer (Server-001).
              > The first file is called TestTwo.asp and is in the root/folder:
              > Connections
              >
              > It connect is below:
              >
              > <%
              > ' FileName="Connection_odbc_conn_dsn.htm"
              > ' Type="ADO"
              > ' DesigntimeType="ADO"
              > ' HTTP="false"
              > ' Catalog=""
              > ' Schema=""
              > Dim MM_TestTwo_STRING

              I do hope you quickly go back to that server and change your password if not
              both the username and password as you just broadcast the login info to the
              world :(



              • 4. Re: Logon to a SQL Server
                bregent Most Valuable Participant
                >I do hope you quickly go back to that server and change your password if not
                >both the username and password as you just broadcast the login info to the
                >world :(

                Ouch!
                • 5. Re: Logon to a SQL Server
                  bregent Most Valuable Participant
                  >to all your questions I can see Yes.

                  Well I am confused. On one hand you say that the browser is displaying the ASP code which tells me it's not being parsed by the ASP engine. Is that still happening?

                  Regarding the connection problem, did you create the ODBC DSN on the server? Have you tried using a DSN-less connection?
                  • 6. Re: Logon to a SQL Server
                    Gold-bug Level 1
                    Hi Bregent,

                    The moment I put script between the <% %>, the webpages displays in the frame just code.
                    If I only have style information I got a nice blank page. Even some code that creates buttons, etc. is no problem. i.e. HTML code is fine, but code to access the Database is a no go. I think it has to do with the fact that the webpage tries to access the SQL dbase and bounces. That fact alone creates a malfunction why the frame as part of the webpages doesn't respond properly and presents just the code.

                    I also think that the SQL server first needs to accept the webpages bases on an approved/accepted UID and PSW.

                    So what I'm looking for is the correct script which will open a link to the SQL Dbase.

                    I think something like the following code should do the trick, but for now, no luck.

                    Hope jou have some thoughts.

                    Kind Regards, Carl.
                    • 7. Re: Logon to a SQL Server
                      bregent Most Valuable Participant
                      >I think it has to do with the fact that the webpage tries to access the SQL dbase and bounces.
                      >That fact alone creates a malfunction why the frame as part of the webpages doesn't respond
                      >properly and presents just the code.

                      I doubt it. If that was the fact it would indicate a seriously misconfigured IIS/ASP server. Exposing your server side code as a result of a database error would be a huge security risk.

                      Does other non-database related ASP code execute properly? Is this occurring on a local testing server, or on a remote server or both?
                      • 8. Re: Logon to a SQL Server
                        Gold-bug Level 1
                        Other non-database relate items works fine.

                        I'm using one workstation in a direct link with a server station. On both stations XP is running. On the Server station I have a disk D: within a subdirectory all my intranet pages. On disk E: I have the SQL running. From the Workstation I normally open a page (Server/D:/x.asp) and that works fine. Now the webpage needs to get access to the SQL Dbase (Server/E:/SQLDbase).

                        That's basicly how the network is setup. I'm think of having a separate station for the webpages and a separate for just SQL. Hopefully that makes life easyer.

                        But it should work.

                        P.S. on disk F: an MS Access Dbase is running now and that works fine, no problems at all, but Access is open as hell. SQL needs approvall first i quest!

                        Hope this helps.

                        Thanks, Carl.
                        • 9. Re: Logon to a SQL Server
                          bregent Most Valuable Participant
                          >From the Workstation I normally open a page (Server/D:/x.asp) and that works fine.

                          How are you opening this page? From the network filesystem as you seem to incidicate above, or from the webserver?
                          • 10. Re: Logon to a SQL Server
                            Gold-bug Level 1
                            On my Workstation I have just a shortcut that opens the webpage in a browser and displays the contant that's running op the server.

                            I believe there is a connection. Even on my workstation I can open a virtual desktop and use it as I was working on the server.

                            I still think that the link between the webpages running on the server and the SQL Dbase is not correct. I need to breach that securitylayer to have the webpages getting access to the Dbase content.

                            Carl.
                            • 11. Re: Logon to a SQL Server
                              Level 7
                              Gold-bug wrote:
                              > On my Workstation I have just a shortcut that opens the webpage in a browser
                              > and displays the contant that's running op the server.
                              >
                              > I believe there is a connection. Even on my workstation I can open a virtual
                              > desktop and use it as I was working on the server.

                              Is the page your viewing

                              A) http://Server/E:/SQLDbase

                              or

                              B) //Server/E:/SQLDbase

                              > I still think that the link between the webpages running on the server and the
                              > SQL Dbase is not correct. I need to breach that securitylayer to have the
                              > webpages getting access to the Dbase content.

                              I have been working on ASP and SQL for many years, I have had bad
                              connections before, and failed connections, and not one single instance
                              has ever caused the ASP code to be displayed. I honestly don't think its
                              possible, as its not how it works.

                              Steve
                              • 12. Re: Logon to a SQL Server
                                Gold-bug Level 1
                                Hi Dooza,

                                I'm a novice, and offcourse I have done something wrong. Anyway. Do you have some script that I might use to get connected from an ASP webpage to my SQL Dbase. I think that te problem lays in the Login Name and Password.

                                The code should be something like:

                                Data Source=Server-001\website\MSSQL$HAGIS_SQL_SERVER\Data;Initial Catalog=Hagis Financial Intranet Dbase;User Id=MyLoginName;Password=MyPassword;

                                I appreciate any working code that I might change and get the thing working.

                                Kind Regards, Carl.
                                • 13. Re: Logon to a SQL Server
                                  Level 7
                                  Gold-bug wrote:
                                  > Hi Dooza,
                                  >
                                  > I'm a novice, and offcourse I have done something wrong. Anyway. Do you have
                                  > some script that I might use to get connected from an ASP webpage to my SQL
                                  > Dbase. I think that te problem lays in the Login Name and Password.
                                  >
                                  > The code should be something like:
                                  >
                                  > Data Source=Server-001\website\MSSQL$HAGIS_SQL_SERVER\Data;Initial
                                  > Catalog=Hagis Financial Intranet Dbase;User Id=MyLoginName;Password=MyPassword;
                                  >
                                  > I appreciate any working code that I might change and get the thing working.

                                  Hi Carl,
                                  I would use Dreamweaver to create the connection, that will sort out
                                  that. To help with creating the connection string I always use this
                                  website: http://www.connectionstrings.com/

                                  Steve
                                  • 14. Re: Logon to a SQL Server
                                    bregent Most Valuable Participant
                                    Carl, can you please answer the question that I asked before? That is, how are you opening this page in the browser? Is it like this:
                                    A) http://myserver/mypage.asp
                                    or
                                    B) //myserver/E:/mypage.asp

                                    Or you can simply paste the url of the page displayed in your browser's address bar into your reply.

                                    The results you are getting indicate to me that the page is not being opened via the web server.
                                    • 15. Logon to a SQL Server
                                      Gold-bug Level 1
                                      This is the URL to connect to the page itself.

                                      \\Server-001\website\MSSQL$HAGIS_SQL_SERVER\Data\MyPage.asp

                                      It is opened with Windows Internet Explorer.

                                      Greetz Carl.
                                      • 16. Re: Logon to a SQL Server
                                        Level 7
                                        Gold-bug wrote:
                                        > This is the URL to connect to the page itself.
                                        >
                                        > \\Server-001\website\MSSQL$HAGIS_SQL_SERVER\Data\MyPage.asp

                                        That is why its not working, its not being served up by IIS. Your page
                                        needs to be in an IIS site, its the only way ASP pages will be processed
                                        and displayed correctly.

                                        Is IIS available on Server-001? Check by browsing using
                                        http://Server-001/ in your browser.

                                        Does this work? Great, using the IIS management console find the home
                                        directory. That is the root folder. If you put your ASP pages there you
                                        will see them being served correctly.

                                        IIS is not on that server, do you have one that does have it available?
                                        Your ASP pages don't have to be on the same server as SQL, as your
                                        connection string connects you to it, as long as your network allows it.

                                        Steve
                                        • 17. Re: Logon to a SQL Server
                                          bregent Most Valuable Participant
                                          >\\Server-001\website\MSSQL$HAGIS_SQL_SERVER\Data\MyPage.asp

                                          That is your problem. ASP is a server side scripting language. The page must be served via IIS so that the ASP script will be executed. When you open the page up directly from the filesystem, the ASP code is not being executed. Now, you may also have a connection problem, but you need to address this first. Open the page up via the IIS from the webserver and see what happens. In other words, the url should look something like http://Server-001/website/MSSQL$HAGIS_SQL_SERVER\Data\MyPage.asp although that would depend on how your files are mapped within IIS.
                                          • 18. Re: Logon to a SQL Server
                                            Gold-bug Level 1
                                            Bregent,

                                            Thanks, what I realized was that there lays one problem, so what I did was returning to the working webpages of my Intranetsite and trying to access the data. It's save to say that one problem is solved because the script is no longer shown and actual the ASP script is asking for information. Not yet the right result, because this code:

                                            Recordset1.ActiveConnection = MM_TestTwo_STRING

                                            where as MM_TestTwo_STRING refers to:

                                            Dim MM_TestTwo_STRING
                                            MM_TestTwo_STRING = "dsn=Hagis SQL Server;uid=MyLogin;pwd=MyPassword;"

                                            returns now a faultcode:



                                            HTTP 500.100 - Internal Server Error - ASP-fout
                                            Internet Information Services

                                            --------------------------------------------------------------------------------

                                            Soort fout:
                                            Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
                                            [Microsoft][ODBC-stuurprogrammabeheer] The name of the source could not be found

                                            /MyPage.asp, line 15

                                            So....... something is now blocking the actual scripting.

                                            Thanks so far, maybe you can help with this puzzle as well.

                                            Carl.

                                            • 19. Re: Logon to a SQL Server
                                              bregent Most Valuable Participant
                                              >[Microsoft][ODBC-stuurprogrammabeheer] The name of the source could not be found

                                              Did you actually create the ODBC datasource 'Hagis SQL Server' from the ODBC Administrator on the server running IIS?
                                              • 20. Re: Logon to a SQL Server
                                                Gold-bug Level 1
                                                Bregent,

                                                First to answer your question. I completely made all the connections again with the ODBC manager installed on the server. I changed the name from Server-001 to Server001, because I don't know for sure is the minus in the name could cause a problem.

                                                What I know get is a massage that the SQL server not exists, or that there is no access to the serve":


                                                Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
                                                [Microsoft][ODBC SQL Server Driver][DBNETLIB]De SQL-server bestaat niet of de toegang tot de server is geweigerd

                                                I think this has to do with the UID and Password.

                                                I tried all kinds of methods with

                                                Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

                                                Does this need to be within the <% %> instruction?
                                                Is Driver={SQL Native Client}; correct, because I got a malfunction on { or a text that the end of an instruction is expected.

                                                It seems that I just need to connect to the Database, I think that a connection to the server is made now.

                                                Looking forward to hear from you,

                                                kind regards, Carl.
                                                • 21. Re: Logon to a SQL Server
                                                  bregent Most Valuable Participant
                                                  What version of MS SQL are you running? This site may help you construct a suitable DSN-less connection string:
                                                  http://www.connectionstrings.com/default.aspx
                                                  • 22. Re: Logon to a SQL Server
                                                    Gold-bug Level 1
                                                    Bregent,

                                                    First: I had SQL Server 2000 Developer Edition, fully installed, then I had MS SQL Server 2005 run over it. When I open my SQL Server Management Studio Express I can connect to SQL Server 2005.

                                                    I've tried the site connectionstrings.com, but there are to many options.

                                                    For instance, do or don't i need the Native Client:

                                                    Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

                                                    But first of all must it in HTML or in code between <% %>

                                                    What is the Native Client Anyway?

                                                    Your talking about DSN less........ what does it (mean).

                                                    Hope you might help me out.

                                                    Kind regards, Carl.
                                                    • 23. Re: Logon to a SQL Server
                                                      Level 7

                                                      "Gold-bug" <webforumsuser@macromedia.com> wrote in message
                                                      news:g7q096$q8$1@forums.macromedia.com...
                                                      > Bregent,
                                                      >>
                                                      > But first of all must it in HTML or in code between <% %>

                                                      typically you will have a Connections folder with a file there that holds
                                                      the connection string info.

                                                      > Your talking about DSN less........ what does it (mean).

                                                      it means not using a DSN for your connection. If you're not familiar with
                                                      DSNs, just open up DW's Help file and type in DSN in the search box to get a
                                                      list of articles. For more info on DSN-less connections in DW, just type in
                                                      DSN-less in that same search box. It will give you info on how to create
                                                      one.

                                                      Your connection string that you would enter in would be something like:
                                                      Provider=SQLOLEDB;Server=nameofmydatabaseserver.com;Database=nameofmydatabase;UID=myconnec tionusername;PWD=myconnectionpassword



                                                      • 24. Re: Logon to a SQL Server
                                                        bregent Most Valuable Participant
                                                        >For instance, do or don't i need the Native Client:

                                                        I would not use the Native Client unless you know for sure that it is installed and configured correctly for use with ASP. I suggest first trying the connection syntax that Hunter provided. If that doesn't work, try this:
                                                        MM_DB_STRING = "Provider=SQLOLEDB.1;Password=xxxxxx;Persist Security Info=True;User ID=xxxxx;Initial Catalog=myDataBase;Data Source=xxx.xxx.xxx.xx"

                                                        Note that the datasource may be entered by either using the hostname or IP. My host does not resolve hostnames for the database so ip must be used. YMMV. Also note the .1 after the SQLOLEDB. I can't recall what this is for, but I believe it was necessary in our environment.
                                                        • 25. Re: Logon to a SQL Server
                                                          Gold-bug Level 1
                                                          Alright,

                                                          I'm now passed the ODBC problem (I guess). I suppose that the connection is made and that ASP is looking into the Dbase. Upon now I got the following message.

                                                          Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
                                                          [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.BullionProductName'.

                                                          The funny thing is that when I use Dreamweaver MX to test the connection and the Database I got the result of a list of productnames. So using Dreamweaver under Application; Database and Bindings I have a list of actual results. If you put it into an ASP-page and upload it to the webserver, then it won't work.

                                                          I suspect that the workstation has an authorization to the SQL server via Dreamweaver, but for some reason it's not passed on to the webpage, although Dreamweaver should do this automatically. It knows where the Local, Remote and Testing Servers are located.

                                                          Still looking for the right solution, but there is light in the tunnel.

                                                          Greetz, Carl.
                                                          • 26. Re: Logon to a SQL Server
                                                            bregent Most Valuable Participant
                                                            >I'm now passed the ODBC problem (I guess). I suppose that the connection is made
                                                            >and that ASP is looking into the Dbase. Upon now I got the following message.

                                                            What connection string did you use to resolve the connection problem? What is the line of code that is causing that error message?