Bill Witherspoon wrote: > Carl Karsten wrote: >> Bill Witherspoon wrote: >>> Carl Karsten wrote: >>>> Bill Witherspoon wrote: >>>>> Carl Karsten wrote: >>>>>> Bill Witherspoon wrote: >>>>>>> Carl Karsten wrote: >>>>>>>> Ed Leafe wrote: >>>>>>>>> On Jun 13, 2007, at 2:16 PM, Carl Karsten wrote: >>>>>>>>> >>>>>>>>>> -> We're using >> Windows >> Authentication currently, >>>>>>>>>> >>>>>>>>>> I think that is the 'problem.' >>>>>>>>> Why? All that means is that SqlServer will authenticate the >>>>>>>>> username/ >>>>>>>>> pw through Windows authentication. If the username/pw is legit, it >>>>>>>> When using win, you don't need to pass a user/pw, and I think you need >>>>>>>> to tell >>>>>>>> it which method to use. so if anything, passing a user/pw may be the >>>>>>>> way to >>>>>>>> tell it to use SQL auth. >>>>>>>> >>>>>>>>> should be fine. In fact, when I helped test pymssql for Dabo, I used >>>>>>>>> Windows authentication to my SQL Server. >>>>>>>> Was your server set to win-only or mixed? >>>>>>>> >>>>>>>> Carl K >>>>>>>> >>>>>>> Carl, >>>>>>> It's set to mixed. Let me try to remove the user/pass right now. >>>>>> If it is set to mixed, then I would try to use SQL, not win. >>>>>> >>>>>>> Well, that's weird - if you leave the user/pass parameters it looks like >>>>>>> it defaults to user = "sa". No go there either. >>>>>> yeah, I think there is a parameter you need to pass that explicitly >>>>>> tells it to >>>>>> use Win. >>>>>> >>>>>>> I also just tried user='billw' with no password, and password=''. No go. >>>>>> Is that a user/pw that the server knows about, or a win user? >>>>>> >>>>>> If you /join #dabo on irc.freenode.net I'll setup a server setup you can >>>>>> hit. >>>>>> >>>>>> Carl K >>>>>> >>>>>> >>>>> Thanks Carl, >>>>> That's a generous offer - I'll take you up on it if it comes to that! >>>> I am around tonight and tomorrow morning. some time in the afternoon I >>>> will be >>>> out. it isn't that generous - I just need to tweak my firewall.conf, >>>> restart, >>>> give you a user/pw, wait for the test and tweak it back when we are done. >>>> that >>>> explanation probably just doubled the amount of work :) >>>> >>>>> Since I last posted, I tried changing the SQLDB to Windows >>>>> Authentication only from Mixed. That didn't seem to help. >>>> flip it back, make sure you know what your SA pw is and test with that. >>>> >>>>> The user/pass I'm using is my own domain user/pass (it's not a local >>>>> machine user/pass if that's what you mean). As for the server knowing >>>>> about it, I guess I assumed that it was querying our Domain Controller >>>>> behind the scenes? >>>> I don't think it is that 'simple', where the use of simple is being >>>> stretched... >>>> >>>> win auth is something like: log into your work station using a user/pw >>>> that gets >>>> authenticated somewhere, somehow, and then when you try to connect to the >>>> sql >>>> server it uses a token (or something your work station was given) to >>>> identify >>>> yourself to sql. I don't think sql ever sends your user/pw anywhere, cuz >>>> I >>>> don't think it ever gets it. >>>> >>>>> I've resisted trying to set up SQL authentication because I didn't want >>>>> the headache of managing another set of users in addition to our AD >>>> SQL auto is simple: you setup a user/pw on the server, and you use that >>>> user/pw >>>> when you log in. I have never had to deal with enough users for it to be >>>> a >>>> headache. >>>> >>> Carl, >>> >>> I did get this working using SQL auth. I can return records from my >>> database using pymssql manually. So finally, I think we can eliminate >>> the SQL Server side of the equation. >> Using what code? >> >> Is the server now set to Win or Mixed? >> >>> Unfortunately, the connection dialog still returns "Unable to Connect". >> My guess is seeing the code you used will bring to light a problem with the >> dabo >> connection stuff. >> >>> When I check the .cnxml file I noticed that it's hashing the password. >>> That's good for obvious reasons - but it's the only thing I see >>> different than my hand coded connection string (I'm passing the password >>> in plain text). >> huh? passing the pw doesn't sound like windows auth. >> >>> I did try hand editing the .cnxml file, but that didn't work. The >>> connection dialog doesn't seem to like the file anymore. >>> >>> Any more ideas? (I would suggest Postgres, but the decision to use >>> SqlServer was corporate). >> suggestion: slow down :) >> >> here is the plan: >> 1: connect using just a few python commands (I think you have done that) >> 2: connect using just a few simple dabo calls (no gui things) >> 3: connect using dabo calls that are similar to how dabo's cd drives things >> 4: eventually use the CD. >> >> And show your work after each step :) >> >> Also, are you a VFP guy? if so, we may try a few VFP lines. >> > > Nope - not a VFP guy (took me a minute to figure out what that is). More > of a Python/WxPython-have-to-pretend-to-know-Windows-at-work-but-really > -run-linux-at-home-cause-I-can't-afford-Mac ;-)
No prob. another thing to play with is osql.exe - the MsSql command line client. That should give you a good idea of what you can/can't do. try this: c:\>osql -S 192.168.51.26 -E you should get: 1> type "exit" enter to exit. also try it with c:\>osql -S 192.168.51.26 -U plant_user -P emseal01 May or may not work, depending on 'suff' :) > > The python code that works is what Ed suggested earlier: > > import pymssql > conn = pymssql.connect(host="192.168.51.26", user='plant_user', > password="emseal01", database="testing") According to the docs, that is not using what I think we are calling win auth: Found some info: http://www.freetds.org/userguide/domains.htm """ Domain logins can be used only with TDS protocol versions 7.0 and 8.0. As mentioned in the installation chapter, Microsoft SQL Server includes the ability to use domain logins instead of standard server logins. The advantage of doing this is that the passwords are encrypted on the wire using a challenge-response protocol. FreeTDS began supporting domain logins in version 0.60. To use domain logins, use the 'DOMAIN\username' syntax for the username and use the domain password. When FreeTDS sees the "\" character, it automatically chooses a domain login. """ So it looks like you need to pass 'something' with a \ in it. Personally, I would try it with just a \: (which needs to be escaped, so use 2) import pymssql conn = pymssql.connect(host="192.168.51.26", user='\\', database="testing") (you will either get a >>> or a big error- no need to run queries) for kicks (cuz the docs may be wrong) try conn = pymssql.connect(host="192.168.51.26", database="testing") > I've switched the SQL box back to mixed mode, and added the SQL user > noted in the code above. This works and returns records from the > po_master table. Why did you do that? you are going backwards :) Switch it back to Win and try the above line. report the results of both tries. make sure sa is the only user on the SQL server. Even if you can connect osql.exe witout a user, I think you need to pass something to pymssql.connect( cuz if you look at the code (below) you will see that if you don't pass a user,pw, it uses 'sa','' > > When I drop the same user/pass into the connection dialog it doesn't > work. That's when I noticed that the password is being hashed in the > .cnxml file. > > I though maybe I should just give up on following the tutorial like you > suggest, and just dive into some code - but it seemed like a good way to > get started, plus I thought I could save some grunt work by leveraging > the tools. yeah, slow... d o w n.... I'm not convinced you are doing Win auth yet. personally, I think this is enough of a headache to just use SQL auth. but I'll keep playing as long as you are :) Carl K # connects to a database def connect(dsn = None, user = "sa", password = "", host = ".", database = "master"): # first get params from DSN dbhost = "" dbbase = "" dbuser = "" dbpasswd = "" dbopt = "" dbtty = "" try: params = string.split(dsn, ":") dbhost = params[0] dbbase = params[1] dbuser = params[2] dbpasswd = params[3] dbopt = params[4] dbtty = params[5] except: pass # override if necessary if user != "": dbuser = user if password != "": dbpasswd = password if database != "": dbbase = database if host != "": dbhost = host # empty host is localhost if dbhost == "": dbhost = "." if dbuser == "": dbuser = "sa" # open the connection con = _mssql.connect(dbhost, dbuser, dbpasswd) con.select_db(dbbase) return pymssqlCnx(con) _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users Searchable Archives: http://leafe.com/archives/search/dabo-users This message: http://leafe.com/archives/byMID/dabo-users/[EMAIL PROTECTED]
