Sql Server trusted connection

[tryit]

I prefer using Dabo for a simple CRUD app using SqlServer DB, however it appears that Dabo doesn't implement trusted connections through pymssql.  This article suggests its possible: http://pymssql.sourceforge.net/architecture.php

Is there presently a workaround for this within Dabo?


[CONFIDENTIALITY AND PRIVACY NOTICE]

Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records.
 
To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser: http://emaildisclaimer.medtronic.com

_______________________________________________
Post Messages to: Dabo-dev@...
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/9A745764A58AD94D996A055D92D7012BD4827958@...

[John-681]

On Tuesday 03 November 2009 04:50:43 am Steffl, Joseph wrote:
> I prefer using Dabo for a simple CRUD app using SqlServer DB, however it
> appears that Dabo doesn't implement trusted connections through pymssql.
> This article suggests its possible:
> http://pymssql.sourceforge.net/architecture.php
>
> Is there presently a workaround for this within Dabo?

You can always build the connectInfo string manually (see below).  

Also I submitted for review a few code changes to the framework to allow the
CxnEditor to create "extra settings " in the connection file.  See ticket
1294.  

from connectInfo import ConnectInfo
        from dConnection import dConnection

        ci = ConnectInfo('MySQL')
        ci.host = 'paulmcnett.com'
        ci.dbName = "house"
        ci.user = 'dabo'
        ci.password = 'dabo'

        conn = dConnection(ci).getConnection()


Johnf

_______________________________________________
Post Messages to: Dabo-dev@...
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/200911030547.58461.jfabiani@...

[tryit]

If I'm running AppWizard do I have the ability to connectInfo string manually ?

-----Original Message-----
From: dabo-dev-bounces@... [mailto:dabo-dev-bounces@...] On Behalf Of John
Sent: Tuesday, November 03, 2009 7:48 AM
To: Dabo developers' list
Subject: Re: [dabo-dev] Sql Server trusted connection

On Tuesday 03 November 2009 04:50:43 am Steffl, Joseph wrote:
> I prefer using Dabo for a simple CRUD app using SqlServer DB, however
> it appears that Dabo doesn't implement trusted connections through pymssql.
> This article suggests its possible:
> http://pymssql.sourceforge.net/architecture.php
>
> Is there presently a workaround for this within Dabo?

You can always build the connectInfo string manually (see below).  

Also I submitted for review a few code changes to the framework to allow the CxnEditor to create "extra settings " in the connection file.  See ticket 1294.  

from connectInfo import ConnectInfo
        from dConnection import dConnection

        ci = ConnectInfo('MySQL')
        ci.host = 'paulmcnett.com'
        ci.dbName = "house"
        ci.user = 'dabo'
        ci.password = 'dabo'

        conn = dConnection(ci).getConnection()


Johnf

[excessive quoting removed by server]

_______________________________________________
Post Messages to: Dabo-dev@...
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/9A745764A58AD94D996A055D92D7012BD4827ADB@...

[John-681]

On Tuesday 03 November 2009 07:10:52 am Steffl, Joseph wrote:
> If I'm running AppWizard do I have the ability to connectInfo string
> manually ?

AppWizard creates a py script so I'm sure it can be done.  I just don't know
where in the code to do it.  I don't use AppWizard to create programs - so I
hope Paul (the author of AppWizard) has an better answer.

Johnf

_______________________________________________
Post Messages to: Dabo-dev@...
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/200911030756.25373.jfabiani@...

[Paul McNett]

John wrote:
> On Tuesday 03 November 2009 07:10:52 am Steffl, Joseph wrote:
>> If I'm running AppWizard do I have the ability to connectInfo string
>> manually ?
>
> AppWizard creates a py script so I'm sure it can be done.  I just don't know
> where in the code to do it.  I don't use AppWizard to create programs - so I
> hope Paul (the author of AppWizard) has an better answer.

AppWizard creates pretty bare-bones dabo applications. If you want to do your own db
connection without using the .cnxml file, that's easy:

1) delete db/default.cnxml
2) in your main.py, before importing any bizobjs but after instantiating the app:

   app.openDatabase()  ## or whatever you want to call it

3) in App.py, add the openDatabase() method and do whatever you want:

   def openDatabase(self):
     self.dbConnection = dabo.db.connect(DbType="MsSQL", Host="example.com", ...)

Paul

_______________________________________________
Post Messages to: Dabo-dev@...
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/4AF072DD.4010807@...

[Ed Leafe]

On Nov 3, 2009, at 12:13 PM, Paul McNett wrote:

>>> If I'm running AppWizard do I have the ability to connectInfo string
>>> manually ?
>>
>> AppWizard creates a py script so I'm sure it can be done.  I just  
>> don't know
>> where in the code to do it.  I don't use AppWizard to create  
>> programs - so I
>> hope Paul (the author of AppWizard) has an better answer.
>
> AppWizard creates pretty bare-bones dabo applications. If you want  
> to do your own db
> connection without using the .cnxml file, that's easy:

        What about if the only way to connect to the server is through an SSL  
connection? AppWizard won't be able to get the basic table info to  
create the app.

        John proposed a catch-all solution, but I think that's going to be  
hell to implement and maintain. What we really need is a list of any  
of the "extra" parameters that we need to support for each backend,  
and the format that the server expects to get them. This way we can  
update the CxnEditor to properly display that appropriate options for  
each backend.


-- Ed Leafe




_______________________________________________
Post Messages to: Dabo-dev@...
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/D8FC982A-EAD3-467A-8E47-6C109D1C1E24@...

[Paul McNett]

Ed Leafe wrote:

> On Nov 3, 2009, at 12:13 PM, Paul McNett wrote:
>
>>>> If I'm running AppWizard do I have the ability to connectInfo string
>>>> manually ?
>>> AppWizard creates a py script so I'm sure it can be done.  I just  
>>> don't know
>>> where in the code to do it.  I don't use AppWizard to create  
>>> programs - so I
>>> hope Paul (the author of AppWizard) has an better answer.
>> AppWizard creates pretty bare-bones dabo applications. If you want  
>> to do your own db
>> connection without using the .cnxml file, that's easy:
>
>
> What about if the only way to connect to the server is through an SSL  
> connection? AppWizard won't be able to get the basic table info to  
> create the app.

Right but we were talking about modifying the generated files to support creating the
connection info manually, which is what I answered.

That said, I wasn't sure what if anything this would solve, other than perhaps
getting any extra kwargs sent along to the adapter, but I don't think it works that way.


> John proposed a catch-all solution, but I think that's going to be  
> hell to implement and maintain. What we really need is a list of any  
> of the "extra" parameters that we need to support for each backend,  
> and the format that the server expects to get them. This way we can  
> update the CxnEditor to properly display that appropriate options for  
> each backend.

I don't like the "extra" parameters thing at all. If postgressql takes 'sslmode' and
mysql takes 'sslstyle' and MS SQL takes 'HKEY_SECURE_SOCKETS_LAYER_VERSION_IN_USE'
(<g>) none of that should be exposed to our appdevs. It should just be 'ssl' or
something simple and consistent for the purpose of the cnxml file, and for the
parameters accepted to dabo.db.connect().

That said, I think it would be fine to accept **kwargs to dabo.db.connect() and pass
them along to the adapter, but I don't think any extra parameters should be part of
the cnxml file, unless it becomes necessary to do it that way, which I'm just not
seeing.

Paul


_______________________________________________
Post Messages to: Dabo-dev@...
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/4AF07B4B.2020002@...

[tryit]

In my case I'm beginning with only a command prompt and python call to Appwizard.py.
I have an ip or DNS name along with a trusted connection to a dbname/tablename to hook into.

The wizard dialog could enable username/password textbox controls if I choose the 'SQL Server authentication' radio button and keep the username/password textbox controls disabled if I choose a 'Windows authentication' radio button.

Or, could the AppWizard just allow a text box to use my own complete connection string?

-----Original Message-----
From: dabo-dev-bounces@... [mailto:dabo-dev-bounces@...] On Behalf Of Paul McNett
Sent: Tuesday, November 03, 2009 12:50 PM
To: Dabo developers' list
Subject: Re: [dabo-dev] Sql Server trusted connection

Ed Leafe wrote:

> On Nov 3, 2009, at 12:13 PM, Paul McNett wrote:
>
>>>> If I'm running AppWizard do I have the ability to connectInfo
>>>> string manually ?
>>> AppWizard creates a py script so I'm sure it can be done.  I just
>>> don't know where in the code to do it.  I don't use AppWizard to
>>> create programs - so I hope Paul (the author of AppWizard) has an
>>> better answer.
>> AppWizard creates pretty bare-bones dabo applications. If you want to
>> do your own db connection without using the .cnxml file, that's easy:
>
>
> What about if the only way to connect to the server is through an SSL
> connection? AppWizard won't be able to get the basic table info to
> create the app.

Right but we were talking about modifying the generated files to support creating the connection info manually, which is what I answered.

That said, I wasn't sure what if anything this would solve, other than perhaps getting any extra kwargs sent along to the adapter, but I don't think it works that way.


> John proposed a catch-all solution, but I think that's going to be  
> hell to implement and maintain. What we really need is a list of any  
> of the "extra" parameters that we need to support for each backend,  
> and the format that the server expects to get them. This way we can  
> update the CxnEditor to properly display that appropriate options for  
> each backend.

I don't like the "extra" parameters thing at all. If postgressql takes 'sslmode' and
mysql takes 'sslstyle' and MS SQL takes 'HKEY_SECURE_SOCKETS_LAYER_VERSION_IN_USE'
(<g>) none of that should be exposed to our appdevs. It should just be 'ssl' or
something simple and consistent for the purpose of the cnxml file, and for the
parameters accepted to dabo.db.connect().

That said, I think it would be fine to accept **kwargs to dabo.db.connect() and pass
them along to the adapter, but I don't think any extra parameters should be part of
the cnxml file, unless it becomes necessary to do it that way, which I'm just not
seeing.

Paul


[excessive quoting removed by server]

_______________________________________________
Post Messages to: Dabo-dev@...
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/9A745764A58AD94D996A055D92D7012BD48DFD04@...

[John-681]

On Tuesday 03 November 2009 10:20:21 am Ed Leafe wrote:
> What about if the only way to connect to the server is through an SSL  
> connection? AppWizard won't be able to get the basic table info to  
> create the app.
>
>         John proposed a catch-all solution, but I think that's going to be
>   hell to implement and maintain. What we really need is a list of any of
> the "extra" parameters that we need to support for each backend, and the
> format that the server expects to get them. This way we can update the
> CxnEditor to properly display that appropriate options for each backend

But what happens when there is a new parameter for some DB or we add a new DB
adapter and it requires again different 'extra' parameters?  All my patch
does is pass the 'extra' parameters on to the unlying DB adapter.  And in
this case (passing Trusted = True) it would work without any additional work.  
The only issue I discovered with the patch is that Ed also
passed 'ForceCreate' via the same means.  It caused me to pop() Ed's
parameter off the stack.  Actually, I don't understand why Ed passed the
parameter to the all the DB adapters when it only applies to SQLite.

I thought my "catch all" solution better than attempting to understand each of
the needs of all the DB adapters.

BTW I believe SQLAlchemy does not support all the special features of each of
the DB's it supports i.e. no SSL.

Johnf

_______________________________________________
Post Messages to: Dabo-dev@...
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/200911031215.01068.jfabiani@...

[John-681]

On Tuesday 03 November 2009 11:11:14 am Steffl, Joseph wrote:

> In my case I'm beginning with only a command prompt and python call to
> Appwizard.py. I have an ip or DNS name along with a trusted connection to a
> dbname/tablename to hook into.
>
> The wizard dialog could enable username/password textbox controls if I
> choose the 'SQL Server authentication' radio button and keep the
> username/password textbox controls disabled if I choose a 'Windows
> authentication' radio button.
>
> Or, could the AppWizard just allow a text box to use my own complete
> connection string?

Oh a catch 22 you can't use AppWizard because it needs to connect to the DB
and you can't connect to DB because it requires Trusted = True.

How about installing MsSQL Express on your machine, create a copy of the real
DB, create the AppWizard script.  Then edit the script to change the
connectinfo to allow you to connect to the real DB.

I hope this helps.

Johnf



_______________________________________________
Post Messages to: Dabo-dev@...
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/200911031232.08626.jfabiani@...

[Ed Leafe]

On Nov 3, 2009, at 2:32 PM, John wrote:

>> Or, could the AppWizard just allow a text box to use my own complete
>> connection string?
>
> Oh a catch 22 you can't use AppWizard because it needs to connect to  
> the DB
> and you can't connect to DB because it requires Trusted = True.
>
> How about installing MsSQL Express on your machine, create a copy of  
> the real
> DB, create the AppWizard script.  Then edit the script to change the
> connectinfo to allow you to connect to the real DB.

        No, the answer is to get a better understanding of just what each  
backend would need, and adjust the interface dynamically. That's why a  
general "pass the kwargs" solution is not a good idea: we need to know  
what the options are.

> The only issue I discovered with the patch is that Ed also
> passed 'ForceCreate' via the same means.  It caused me to pop() Ed's
> parameter off the stack.  Actually, I don't understand why Ed passed  
> the
> parameter to the all the DB adapters when it only applies to SQLite.
       
        Actually, the fact that the blindly-passed parameter required you to  
do all sorts of cleanup code should have been a tip-off that it isn't  
a good solution. The reason that forceCreate was done that way was  
that it was a hastily-thrown-together approach to solve a particular  
problem. Now that we have seen that we need to pass even more  
information, it's obvious that a refactoring is needed, and that  
forceCreate and any other params need to be explicit.


-- Ed Leafe




_______________________________________________
Post Messages to: Dabo-dev@...
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/06387923-201F-47B6-B66C-481EDC818527@...

[John-681]

On Tuesday 03 November 2009 01:11:58 pm Ed Leafe wrote:

> How about installing MsSQL Express on your machine, create a copy of  
> the real
> DB, create the AppWizard script.  Then edit the script to change the
> connectinfo to allow you to connect to the real DB.

I was trying to provide a way for the poster to solve his current issue ( a
work around for now).  So you are responding to the wrong email right?

> No, the answer is to get a better understanding of just what each  
> backend would need, and adjust the interface dynamically. That's why a  
> general "pass the kwargs" solution is not a good idea: we need to know  
> what the options are.



I think you are trying to answer my other questions as to why my patch is not
the way to go.  

I don't disagree that it would be nice to have a dynamic solution for each of
the DB's.  Passing the 'extra' via the **kwargs lacks error checking etc.  
But it brings up other questions.

I don't think hard coding the 'extra' parameters is right.  Although, it might
just be just another method of the DB adapter.  It's just that they (the
parameters) can change depending on the version of the DB.  

For example SSL was not available for earlier versions of Postgres.  Meaning
how will Dabo know what version of the 'extra' parameter to use without
making a connection.  And it can't make the connection without the 'extra'
parameter.  I guess we could supply several different DB adapters based on
the DB versions.  Something a kin to the windows "Data Source Administrator"
and the Drivers tab which includes all the versions. But even "Data Source
Administrator" has "Other" to create the Network libraries

Over time there will be plenty of changes and new features for each of the
DB's Dabo supports.  And then there is all the possible values for each of
the 'extra' parameters i.e Postgres's SSL has four.  The 'extra' parameters
and the values can get complex in a hurry.  Meaning a maintenance PIA.

If we were talking about any other prg I'd say "sounds like we need a
database" to hold all the different options and their values.


Johnf

_______________________________________________
Post Messages to: Dabo-dev@...
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/200911031443.17648.jfabiani@...