I am scratching my head here and could use a little help. I have just installed a Win2016 server with MSSQL 2012 and Lucee Lucee 5.2.8.50 and so far I have been unsuccessful in getting Lucee to create a database connection.
MSSQL was installed with dual authentication. The Firewall is off (dev box) and I have made sure that TCP is enabled and port 1433 is configured in the SQL server manager for both the Localhost and the server IP. I have triple checked the sa password. I have copied a database from a SQL2000 server and and even verified that a connection and full access can be made from an old CF5 server box connecting by IP to the new SQL2012 server.
When I try to set up the Database connection in Lucee I have selected the Microsoft SQL Server (Vendor Microsoft) (Installed) version 6.2.2.jre8 and when trying to connect I get this error (notice the misspelling) âno access to update datsource connectionsâ and I am not sure what it is telling me.
I am assuming there is a permissions issue but I am not sure where to look. Any assistance would be appreciated.
First I will say we have the exact same configuration on one of our servers (Win 2016 / MSSQL 2012 / Lucee 5) and connect without issues so no need to be concerned with any compatibility issues. In my experience 99/100 times it is a configuration issue in SQL Server Configuration Manager. Look in there to make sure you have TCP/IP Protocol enabled in all areas and check your dynamic port is set to 1433 (in your case but it can be anything - we change our to alternate port)
I suspected it was something with the SQL server setup but I canât seem to find the usual suspects. I have TCP enabled and the defaut was for the dynamic ports setting was blank. I set them to 1433 just in case but this doesnât correct this issue.
I searched for the specific error âno access to update datsource connectionsâ which with the misspelling makes it unique and it seems to be found in the â* update PSQ Stateâ portion of the Lucee code. which I found here: Lucee/XMLConfigAdmin.java at master ¡ lucee/Lucee ¡ GitHub
So it seems to be a securityexception issue but I am not sure where start in fixing it. What is determining the hasaccess state?
This is just one area, make sure you have checked all places where the protocols are enabled/disabled from
Also in this screen make sure you scroll all the way to the bottom and ensure this last port is setup to 1433.
Of course ensure the ip you are using is enabled. Finally you can easily test this by logging in to SQL Server Management Studio using the same connection ip and credentials and see if it works.
Here is the thing that has me confused. I checked all those settings which is why I posted them (all local.private IP numbers and also the IPALL settings which is why I posted the image. I can login to SQL 2012 with the SQL Server Management Studio using the same connection ip (127.0.0.1) and sa credentials, I can connect to the SQL 2012 server from a CF5 installation server and access/update the specified database. But when I try to get Lucee to connect it gives me an error that I donât understand. The error it gives me at the to of the page is"
âno access to update datsource connectionsâ
If I donât enter the correct username or password the error it give me is:
âLogin failed for user âsaâ. ClientConnectionId:8b5221b7-3cf9-42c4-a2f0-c6b847a3652eâ
I donât know what can cause that error message so I am not sure where to start looking. Maybe I am misunderstanding the rest of the settings so here is a screen grab of the last attempt showing the relevant Datasource configuration settings.
This should be an easy thing and I know others donât have this issue. I am sure it will be something stupid on my end. I would really like to use this product and I know it can work so any assistance would be greatly appreciated.
Thank you in advance.
PS: I tried at first to connect to our old SQL 2000 server but the error it gave was fully understandable
âSQL Server version 8 is not supported by this driver. ClientConnectionId:0e56c815-4cbb-46fc-bb99-d07804683bf0â
I even tried uninstalling and re-installing Lucee as a hail mary but alas.
That actually gave me a clue or at least a different more meaningful error message. Using the jTDS driver and entering everything as before including the username and password I get the same error.
no access to update datsource connections
however if I omit the Username I get this error:
I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.
Omitting the password I get this error.
Login failed for user âsaâ.
I am not a âJava guyâ. So I let the Lucee installer install the Java Runtime Environment and did not download java separately Is it possible that a needed system environment variable was not set? I ask because of the error Check the java.library.path system property when I omit the username.
Are there debug logs I can check for more information and where do I find and/or enable them?
Addendum: I found the application.log in the C:\lucee\tomcat\webapps\ROOT\WEB-INF\lucee\logs folder and this is last entry when I try to use the MS jdbc driver:
"ERROR","http-nio-8888-exec-9","08/24/2018","00:19:30","",";no access to update datsource connections;lucee.runtime.exp.SecurityException: no access to update datsource connections
at lucee.runtime.config.XMLConfigAdmin.updateDataSource(XMLConfigAdmin.java:1609)
at lucee.runtime.tag.Admin.doUpdateDatasource(Admin.java:2861)
at lucee.runtime.tag.Admin._doStartTag(Admin.java:781)
at lucee.runtime.tag.Admin.doStartTag(Admin.java:335)
at services_datasource_create_cfm1334$cf.call(/admin/services.datasource.create.cfm:75)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:899)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:821)
at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:803)
at services_datasource_cfm414$cf.call(/admin/services.datasource.cfm:109)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:899)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:821)
at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:803)
at web_cfm$cf.call(/admin/web.cfm:430)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:899)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:821)
at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:803)
at server_cfm$cf.call(/admin/server.cfm:2)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:899)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:821)
at lucee.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:225)
at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:43)
at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2462)
at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2452)
at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2425)
at lucee.runtime.engine.Request.exe(Request.java:44)
at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1091)
at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1039)
at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:102)
at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:51)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
at mod_cfml.core.invoke(core.java:152)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:800)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:800)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1471)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
"
After installing the dlls you can use the Other - JDBC Driver datasource
class: com.microsoft.sqlserver.jdbc.SQLServerDriver
connection string: jdbc:sqlserver://servername:1433;DATABASENAME=dbname;integratedSecurity=true
Replace servername with your server and dbname with your database name.
Unselect verify connection. You can verify from the datasources list.
This is not an issue with the SQL server and I am guessing that it is a permissions issue with Windows and Lucee.
To check I installed Lucee on a Windows 2014 server and can use the jTDS connector to connect to both our old SQL 2000 server and the newly installed SQL 2012 server on the dev box. I can also use the MS JDBC driver to connect to the new SQL server. So itâs not the SQL server.
Besides the OS the difference between the test install and the Dev box is that on the DEV box I am not using the \inetpub folder having moved the webs to a different folder. I have given that folder permissions by adding IUSR and IIS_USRS to it and given them full control. (As we have done in the past for IIS 6 & 7)
Are there other permission that are required that Lucee needs when moving the websites to a different folder?
Are there permission required for whatever folder Lucee writes the datasource connections?
Does Lucee require special permission on 2016 that are not required on 2012?
Where does Lucee write/store the datasource connections information?
Linux lucee user here, so I donât know much about IIS setup.
The Web settings are kept in the lucee-web.xml in the WEB-INF/lucee directory wherever your site code is.
The Server are in the lucee-server.xml file in the tomcat/lucee-server/context directory.
What happens if you try to add a datasource and uncheck the verify connection checkbox?
I get the " no access to update datsource connections" error.
The more I look at it the more I am sure there is a permission issue and since I was easily able to get Lucee running on a 2014 box and this one is relatively empty and essentially virgin I am wiping the slate clean and starting over. Format and reinstall everything. It will take less time. I will let people know. I really do appreciate the assistance everyone has given.
If nothing else i did learn things about Lucee I never would have normally. Although I did not find the exact problem. starting over with a completely new install of the OS has resulted in a perfectly working setup. I can now connect to both the old and new SQL servers without problems. I now see that something must have gone wrong with the initial installation that resulted in an errant windows permissions issue.
Thank you to everyone who offered their assistance.
This issue was resolved by a drive format and a complete re-install.
Although I never found the exact cause it is my guess that something went awry with the original installation and somehow either tomcat or lucee did not get the correct windows permissions to write to the where ever the database connection information was stored.
Once I re-installed everything Lucee could connect to both our old and new MSSQL servers which is couldnât do before.
